Oracle treats empty strings as NULL
This is with reference to "NULL can be your friend." You suggest to use COALESCE function and say that "What this COALESCE expression does is substitute an empty string whenever foo is NULL, so that the resulting value will be found in the IN list." However, my results are contrary.
This is with reference to NULL can be your friend (18 August 2005). You suggest to use COALESCE function and say...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
that "What this COALESCE expression does is substitute an empty string whenever foo is NULL, so that the resulting value will be found in the IN list."
However, my results are contrary:
SELECT 'X' FROM DUAL WHERE COALESCE('foo','') IN ('','B','C','D'); no rows selectedSELECT 'X' FROM DUAL WHERE COALESCE(NULL,'') IN ('','B','C','D'); no rows selected
Further, the following SQL shows that Oracle (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production) treats '' as NULL:
SELECT NVL(COALESCE(NULL,''),'XXX') FROM DUALNVL --- XXX
So, is this behavior not carried forward ONLY 10g onwards?
Thanks
The behavior that you have discovered is well-known. Oracle treats an empty string as NULL. This is not standard SQL.
Apparently, this behavior continues. The Oracle 10g Release 1 documentation for Nulls contains this note:
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.
So to come back to the example given, you could also get the desired results by substituting something else for the empty string, like this:
WHERE COALESCE(foo,'~') IN ('~','B','C','D')
The point was to ensure that the result of the WHERE condition will evaluate TRUE both for NULLs as well as for the legitimate values 'B', 'C', and 'D'.
