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.

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.

The following string functions support NULL padding:

Restrictions

The following restrictions apply to SET ANSI_PADDING:

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

0x61000000 
0x61000000 
0x61002000 
0x61002000 
0x61202020 
0x61202020 

See Also

INSERT

UPDATE

String Functions

Conversion Functions