SET ANSI_PADDING

SET ANSI_PADDING

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

Pervasive PSQL supports two interfaces: transactional and relational. The transactional interface allows a CHAR to be padded with NULLs. The relational interface 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.

SET ANSI_PADDING=< ON | OFF >

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 Pervasive Knowledge Base on the Pervasive Web site 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

The following restrictions apply to SET ANSI_PADDING:

•

The statement applies only to character data types of fixed length (CHAR), not to VARCHAR or LONGVARCHAR.

•

The statement applies to the session level.

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

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

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.

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

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\x00\x00\x00

a\x00\x20\x00\x00

a\x00\x20\x00\x00

a\x20\x20\x20\x20

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:

String Functions

Conversion Functions