Q
Problem solve Get help with specific problems with your technologies, process and projects.

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

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 selected
SELECT '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 DUAL
NVL
---
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'.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Meet all of our Oracle Database / Applications experts

View all Oracle Database / Applications questions and answers

Start the conversation

Send me notifications when other members comment.

Register

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close