SET ANSI_PADDING

The SET ANSI_PADDING statement allows the Relational Engine to handle CHAR data types padded with NULLs (binary zeros). CHAR is defined as a character data type of fixed length.

PSQL supports two interfaces: transactional and relational. The MicroKernel Engine allows a CHAR to be padded with NULLs. The Relational Engine conforms to the ANSI standard for padding, which specifies that a CHAR be padded with spaces. For example, by default, a CHAR column created with a CREATE TABLE statement is always padded with spaces.

An application that uses both interfaces may need to process strings padded with NULLs.

Syntax

SET ANSI_PADDING=< ON | OFF >

Remarks

The default value is ON, meaning that strings padded with spaces are inserted into CHARs. Trailing spaces are considered as insignificant in logical expression comparisons. Trailing NULLs are considered as significant in comparisons.

If set to OFF, the statement means that strings padded with NULLs are inserted into CHARs. Both trailing NULLs and trailing spaces are considered as insignificant in logical expression comparisons.

On Windows, ANSI padding can be set to on or off for a DSN through a registry setting. See the PSQL Knowledge Base on the Actian website and search for “ansipadding.”

The following string functions support NULL padding:

CHAR_LENGTH	CONCAT	LCASE or LOWER
LEFT	LENGTH	LOCATE
LTRIM	POSITION	REPLACE
REPLICATE	RIGHT	RTRIM
STUFF	SUBSTRING	UCASE or UPPER

For information on how ANSI_PADDING affects each function, see its scalar function documentation.

Restrictions

The following restrictions apply to SET ANSI_PADDING:

•The statement applies only to the fixed length character data type CHAR, not to NCHAR, VARCHAR, NVARCHAR, LONGVARCHAR or NLONGVARCHAR.

•The statement applies to the session level.

Examples

The following example shows the results of string padding using the INSERT statement with SET ANSI_PADDING set to ON and to OFF.

DROP TABLE t1

CREATE TABLE t1 (c1 CHAR(4))

SET ANSI_PADDING = ON

INSERT INTO t1 VALUES ('a') -- string a = a\0x20\0x20\0x20

INSERT INTO t1 VALUES ('a' + CHAR(0) + CHAR(0) + CHAR(0)) -- string a = a\0x00\0x00\0x00

DROP TABLE t1

CREATE TABLE t1 (c1 CHAR(4))

SET ANSI_PADDING = OFF

INSERT INTO t1 VALUES ('a') -- string a = a\0x00\0x00\0x00

INSERT INTO t1 VALUES ('a' + CHAR(32) + CHAR(32) + CHAR(32)) -- string a = a\0x20\0x20\0x20

============

The following example shows the results of string padding using the UPDATE statement with SET ANSI_PADDING set to ON and to OFF.

DROP TABLE t1

CREATE TABLE t1 (c1 CHAR(4))

SET ANSI_PADDING = ON

UPDATE t1 SET c1 = 'a' -- all rows for c1 = a\0x20\0x20\0x20

UPDATE t1 SET c1 = 'a' + CHAR(0) + CHAR(0) + CHAR(0) -- all rows for c1 = a\0x00\0x00\0x00

DROP TABLE t1

CREATE TABLE t1 (c1 CHAR(4))

SET ANSI_PADDING = OFF

UPDATE t1 SET c1 = 'a' -- all rows for c1 = a\0x00\0x00\0x00

UPDATE t1 SET c1 = 'a' + CHAR(32) + CHAR(32) + CHAR(32) -- all rows for c1 = a\0x20\0x20\0x20

============

The following example shows how a character column, c1, can be cast to a BINARY data type so that you can display the contents of c1 in BINARY format. Assume that table t1 has the following six rows of data:

a\x00\x00\x00\x00

a\x00\x20\x00\x00

a\x20\x20\x20\x20

The following statement casts c1 as a BINARY data type:

SELECT CAST(c1 AS BINARY(4)) FROM t1

The SELECT statement returns the following:

0x61000000

0x61002000

0x61202020