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