Sybase inserts a single space in place of the empty string ''

Question

Apparently when inserting an empty string ('') in a VARCHAR column Sybase (tested in ASE 15.7) inserts a single space character instead. Experimenting I verified that the option ansinull has no bearing on this behavior either way:

> set ansinull on
> create table a (a1 varchar(1))
> insert into a(a1) values ('')
> select a1, len(a1) as 'len(a1)', datalength(a1) as 'datalength(a1)',
  ascii(a1) as 'ascii(a1)', char_length(a1) as 'char_length(a1)'
  from a
> go
(1 row affected)
a1 len(a1)     datalength(a1) ascii(a1)   char_length(a1)
-- ----------- -------------- ----------- ---------------
             1              1          32               1

(1 row affected)
>
>
> drop table a
> go
> set ansinull off
> create table a (a1 varchar(1))
> insert into a(a1) values ('')
> select a1, len(a1) as 'len(a1)', datalength(a1) as 'datalength(a1)',
  ascii(a1) as 'ascii(a1)', char_length(a1) as 'char_length(a1)'
  from a
> go
(1 row affected)
a1 len(a1)     datalength(a1) ascii(a1)   char_length(a1)
-- ----------- -------------- ----------- ---------------
             1              1          32               1

(1 row affected)

Is there any justification / reasoning for this behavior and how can I disable this "feature"? Is this behavior inherited in the SQL Server codebase?

I was bitten by this as my test logic failed since I was doing a .equals() comparison (in the client-side Java code that's using JDBC to read from the database and make certain assertions).

Answer 1

RE: "Is this behavior inherited in the SQL Server codebase?" - No

RE: "Is there any justification / reasoning for this behavior and how can I disable this "feature"?" - Not that I am aware of. This is one of numerous Sybase quirks.

From http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1550/html/blocks/blocks311.htm

The empty string ("") or ('') is interpreted as a single blank in insert or assignment statements on varchar or univarchar data. In concatenation of varchar, char, nchar, nvarchar data, the empty string is interpreted as a single space; for following example is stored as “abc def”:

"abc" + "" + "def"

The empty string is never evaluated as NULL.

From memory: In Sybase you can assign a NULL value to a string and it will be interpreted as empty string. - I could be wrong though.

Another work around is rtrim( '' )