Truncate Trailing Zeros

The disable varbinary truncation configuration parameter controls whether trailing zeros are included at the end of varbinary and binary null data.

By default, disable varbinary truncation is off for the server.

When SAP ASE is set to truncate trailing zeros, tables you subsequently create store the varbinary data after truncating the trailing zeros. For example, if you create the test1 table with disable varbinary truncation set to 0:

create table test1(col1 varbinary(5))

Then insert some varbinary data with trailing zeros:

insert into test1 values(0x12345600)

SAP ASE truncates the zeros:

select * from test1

col1
------------
0x123456

However, if you drop and re-create table test1 and set disable varbinary truncation to 1 (on) and perform the same steps, SAP ASE does not truncate the zeros:

select * from test1

col1
------------
0x12345600

SAP ASE considers data with or without trailing zeros as equal for comparisons (that is, 0x1234 is the same as 0x123400).

Because SAP ASE stores data according to how disable varbinary truncation is currently set, tables may have a mix of data with or without trailing zeros, although the datatype does not change:

If you perform a select into to copy data from one table to another, SAP ASE copies the data as it is stored (that is, if disable varbinary truncation is turned off, the trailing zeros are truncated). For example, using the tables from the examples above, if you disable varbinary truncation, then select data from table test1 into table test2:
```
sp_configure "disable varbinary truncation", 1

select * into test2 from test1
```
Then reinsert the same data again:
```
insert into test2 select * from test1
```
Table test2 does not truncate the trailing zeros because you ran select into with disable varbinary truncation set to 1, and the target table does not inherit the property from the source table. The data in the target table is truncated or preserved, depending on how the configuration parameters were setn when you ran select into:
```
select * from test2
```
```
col1
------------
0x12345600
0x12345600
```
Bulk copy (bcp) inserts data according to how disable varbinary truncation is set on the column when created.
You cannot use alter table to change the truncation behavior for a specific column. However, columns you add with alter table either truncate or preserve trailing zeros according to the value of disable varbinary truncation.
For example, if you create table test3 and column c1 with truncate trailing zeros disabled:
```
sp_configure "disable varbinary truncation", 1
create table test3(c1 varbinary(5))
insert into test3 values(0x123400)
```
c1 retains the trailing zeros:
```
select * from test3
c1
----------
0x123400
```
However, if you enable truncated trailing zeros and use alter table to add a new column, c2:
```
sp_configure "disable varbinary truncation", 0
alter table test3 add c2 varbinary(5) null
insert into test3 values(0x123400, 0x123400)
```
c2 truncates the trailing zeros:
```
select * from test3
c1           c2
----------   ----------
0x123400           NULL
0x123400         0x1234
```

Trailing zeros are preserved in:

Worktables (after disable varbinary truncation is set to 1). The first example below includes a worktable in which the trailing zeros are retained, but in the second example, the worktable stores only the first 6 digits:
```
select 0x12345600 union select 0x123456
-------------
0x12345600
select 0x123456 union select 0x12345600
-------------
0x123456
```

Concatenations. For example:

select 0x12345600 + col1, col1 from test1

                       col1
--------------------      ------------
0x123456001234560000      0x1234560000
0x1234560001234560        0x01234560
0x1234560012345600        0x12345600
0x123456000123456700      0x0123456700

Functions. For example:

select bintostr(0x12340000)

------------
     1234000

order by and group by queries. For example:
```
select col1 from 
(select 0x123456 col1 union all 
select 0x12345600 col1) temp1 order by col1
```
```
col1
----------
0x123456
0x12345600
```
Note: If a query includes worktables, you must enable the disable varbinary truncation configuration parameter before running the query to ensure SAP ASE performs no truncation.
Subqueries – trailing zeros are preserved unless the query involves a worktable, in which case the truncation depends on the value of disable varbinary truncation.
Dumps and loads – if the table data you dump includes trailing zeros, the trailing zeros are preserved when you load that data, regardless of the value of disable varbinary truncation in the target database.
Unions (see example for worktables, above).

convert. For example:

select convert(binary(5), 0x0000001000)
------------
0x0000001000