Q

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

This was first published in September 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close