http://www.perlmonks.org?node_id=390606


in reply to Re: Writing It Myself, DWIM, and Expectations
in thread Writing It Myself, DWIM, and Expectations

The SQL-92 standard makes a distinction between empty string and NULL. It is Oracle that considers empty string and NULL to be equivalent. My impression is that Sybase and old versions of SQL Server behave the same way.
  • Comment on Re^2: Writing It Myself, DWIM, and Expectations

Replies are listed 'Best First'.
Re^3: Writing It Myself, DWIM, and Expectations
by mpeppler (Vicar) on Sep 14, 2004 at 05:55 UTC
    Sybase (and presumably MS-SQL) will silently convert "" to " " as it can't store a zero-length string that is not NULL.

    Michael

[reply]

      I can't check it at this moment, but I don't think any recent MS SQL (that is 7.0 and up) does this for varchars. I'm sure I would notice. It does change an empty string to a string containing the right number of spaces for char(N) columns, but there it can't do anything better. If I still remember this node tomorrow when I am at work and can try things I'll test it and post an update.

      Update: Yep, I was right: 

      create table foo (svarchar varchar(10), schar char(10))

insert into foo (svarchar, schar) values ('', '')
insert into foo (schar) values ('')

select '>' + svarchar + '<', '>' + schar + '<'
from foo

      [download]

      Jenda
      Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.
         -- Rick Osborne

[reply]
[d/l]
        That is the very reason for varchar. And the curious behavior of SQL-92 is because there is no varchar in SQL-92. So you must define your database with char(x) fields and you get the behavior of char(x). Of course.
[reply]

In Section Meditations