Treatment of Trailing Zeros

All BINARY columns are padded with zeros to the full width of the column. Trailing zeros are truncated in all VARBINARY columns.

The following example creates a table with all four variations of BINARY and VARBINARY data types defined with NULL and NOT NULL. The same data is inserted in all four columns and is padded or truncated according to the data type of the column.
CREATE TABLE zeros (bnot BINARY(5) NOT NULL,
        bnull BINARY(5) NULL,
        vbnot VARBINARY(5) NOT NULL,
        vbnull VARBINARY(5) NULL);
INSERT zeros VALUES (0x12345000, 0x12345000,
        0x12345000, 0x12345000);
INSERT zeros VALUES (0x123, 0x123, 0x123, 0x123);
INSERT zeros VALUES (0x0, 0x0, 0x0, 0x0);
INSERT zeros VALUES ('002710000000ae1b',
'002710000000ae1b', '002710000000ae1b',
'002710000000ae1b');
SELECT * FROM zeros;

bnot

bnull

vbnot

vbnull

0x1234500000

0x1234500000

0x12345000

0x12345000

0x0123000000

0x0123000000

0x0123

0x0123

0x0000000000

0x0000000000

0x00

0x00

0x3030323731

0x3030323731

0x3030323731

0x3030323731

Because each byte of storage holds 2 hexadecimal digits, Sybase IQ expects binary entries to consist of the characters “0x” followed by an even number of digits. When the “0x” is followed by an odd number of digits, Sybase IQ assumes that you omitted the leading 0 and adds it for you.

Input values “0x00” and “0x0” are stored as “0x00” in variable-length binary columns (VARBINARY). In fixed-length binary columns (BINARY), the value is padded with zeros to the full length of the field:
INSERT zeros VALUES (0x0, 0x0, 0x0, 0x0);
SELECT * FROM zeros 

bnot

bnull

vbnot

vbnull

0x0000000000

0x0000000000

0x00

0x00

If the input value does not include “0x”, Sybase IQ assumes that the value is an ASCII value and converts it. For example:
CREATE TABLE sample (col_bin BINARY(8));
INSERT sample VALUES ('002710000000ae1b');
SELECT * FROM sample;

col_bin

0x3030323731303030

Note: In the above example, ensure you set the string_rtruncation option to “off”.
When you select a BINARY value, you must specify the value with the padded zeros or use the CAST function. For example:
SELECT * FROM zeros WHERE bnot = 0x0123000000;
or :
SELECT * FROM zeros WHERE bnot = CAST(0x0123 as binary(5));