Nulls, Blanks, and Nothings

One mildly interesting difference between Oracle and SQL Server is how they treat 0-length strings. SQL Server treats them as a kind of thing distinct from a NULL value, whereas Oracle views 0-length strings and NULLS as identical. Consider the following queries in SQL Server:

SELECT ISNULL(NULL,'Yeah It''s NULL') [isItNull?];

SELECT ISNULL('','Yeah It''s NULL') AS [isItNull?];

There’s a difference. If you run corresponding queries in Oracle, you get this:

SELECT NVL(NULL,'Yeah It''s NULL') "isItNull?" FROM dual;

SELECT NVL('','Yeah It''s NULL')  "isItNull?" FROM DUAL;

No difference. These systems also diverge when they measure the lengths of strings made up entirely of spaces. These two queries give the same result, 0, when run in SQL Server:

SELECT LEN('') AS stringLength;

SELECT LEN('      ') AS stringLength;

SQL Server simply does not care about trailing spaces. However, in Oracle the corresponding queries give us:

SELECT LENGTH('') "stringLength" FROM DUAL; 

SELECT LENGTH('      ') "stringLength" FROM DUAL; 

Oracle does care about trailing spaces.

Anyway, the differences in how these systems treat 0-length strings and NULLs hasn’t meant too much for me in practice, except my SQL Server WHERE clauses sometimes check to make sure a string is not null and has a length greater than 0. The other day, though, a user alerted me to an error that arose specifically from how SQL Server treats 0-length strings. An expression like this was throwing the following error:

SELECT CONVERT(NUMERIC,'') AS Test

Error converting data type varchar to numeric.

If the application had instead left the column NULL instead of inserting a 0-length string, the query would have run fine.

SELECT CONVERT(NUMERIC, NULL) AS Test

Oracle is able to handle a case like this just fine:

SELECT TO_NUMBER('') "Test" FROM DUAL

For what it’s worth, even though SQL Server runs into an error in this particular case, it is Oracle who recognizes that it’s on the wrong side of history and may need to change:

Note: Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.