5

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).

Marcus Junius Brutus
  • 26,087
  • 41
  • 189
  • 331
  • What happens if you change the column definition to `varchar(1)`? – sticky bit Sep 11 '18 at 21:43
  • same thing, regardless of `ansinull` value – Marcus Junius Brutus Sep 11 '18 at 21:46
  • In SQL Server `len(a1)` is `0`, but it would always be, since `len` does not count trailing spaces. More relevant is that `datalength(a1)` (which counts bytes) is also `0`, showing that the value is truly empty (this is true both under `ANSI_PADDING` on and off, which is more relevant than `ANSI_NULLS`). (And `varchar` is `varchar(1)`, and omitting the length is poor form.) – Jeroen Mostert Sep 11 '18 at 21:47
  • Does `ASCII(a1)` give anything interesting (that is, not `32`)? – Jeroen Mostert Sep 11 '18 at 21:51
  • @JeroenMostert `ASCII(a1)` gives `32` – Marcus Junius Brutus Sep 11 '18 at 21:56
  • For giggles, does using `REPLICATE('banana', 0)` instead of a literal empty string give the same result? And what's the result of `CHAR_LENGTH` and `BYTE_LENGTH`? – Jeroen Mostert Sep 11 '18 at 22:10
  • @JeroenMostert `insert into a(a1) (select replicate('', 1))` yields the same result. OTOH, `insert into a(a1) (select replicate('', 0))` inserts NULL. For the rest, I've updated my question – Marcus Junius Brutus Sep 12 '18 at 15:08
  • And here I thought SQL Server's handling of trailing spaces with `LEN` was bad... but Sybase really goes above and beyond. No doubt changing this is "impossible" now, though, due to the looming threat of big legacy enterprise behemoths that work on Sybase and Sybase alone breaking if this behavior is changed. – Jeroen Mostert Sep 12 '18 at 15:11

2 Answers2

6

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( '' )

Alex
  • 4,885
  • 3
  • 19
  • 39
2

ASE is unique in the DBMS world in that an empty string actually evaluates as a single space. As someone mentioned, NULL acts as a true empty string when you concat it.

RobV
  • 2,263
  • 1
  • 11
  • 7