Thursday, September 25, 2014

Empty string is different from NULL

I've been working with SyBase for 6 years now and it can still surprise me with strange behaviors. Take a look at the following SELECT statement:

In SQLServer (2005 onwards), executing the above script will yield equal results for all the 3 fields:

Copy and Paste the above script in Sybase, run it and you'll get a different result:

The first field is obvious. The second likewise: single quote marks, although empty, do not represent NULL value. Empty is different from NULL. However, the LTRIM and RTRIM return NULL when working on an empty string, as opposite to the SQLServer that returns an empty string. To me it was useful to have the SyBase behaviour in a recent project but, logically speaking, I think SQLServer's method is the best. Empty string is different from null.