Treatment of trailing zeroes

All binary not null columns are padded with zeros to the full width of the column. Trailing zeros are truncated in all varbinary data and in binary null columns, since columns that accept null values must be treated as variable-length columns.

The following example creates a table with all four variations of binary and varbinary datatypes, NULL and NOT NULL. The same data is inserted in all four columns and is padded or truncated according to the datatype of the column.

create table zeros (bnot binary(5) not null,
            bnull binary(5) null,
            vnot varbinary(5) not null,
            vnull varbinary(5) null)

insert zeros values (0x12345000, 0x12345000, 0x12345000, 0x12345000)
insert zeros values (0x123, 0x123, 0x123, 0x123)

select * from zeros

bnot             bnull        vnot        vnull    
------------     ---------    ----------  --------- 
0x1234500000     0x123450     0x123450    0x123450 
0x0123000000     0x0123       0x0123      0x0123

Because each byte of storage holds 2 binary digits, Adaptive Server 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, Adaptive Server 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 (binary null, image and varbinary columns). In fixed-length binary (binary not null) columns, the value is padded with zeros to the full length of the field:

insert zeros values (0x0, 0x0,0x0, 0x0)

select * from zeros where bnot = 0x00

bnot           bnull       vnot        vnull        
----------     ------      -----       ------------
0x0000000000   0x00        0x00        0x00  

If the input value does not include the “0x”, Adaptive Server assumes that the value is an ASCII value and converts it. For example:

create table sample (col_a binary(8))

insert sample values (’002710000000ae1b’)

select * from sample