Author Topic: Question about NULL in Oracle  (Read 119 times)

0 Members and 1 Guest are viewing this topic.

Offline rpmolecule

  • Full Member
  • *
  • Posts: 224
  • Karma: +1/-0
  • Seize the Day!
    • View Profile
Question about NULL in Oracle
« on: December 16, 2008, 07:15:57 AM »
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
 

 
> EXPERT RESPONSE

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

Techronnati | where technology never sleeps

Question about NULL in Oracle
« on: December 16, 2008, 07:15:57 AM »

Mountain View

 

Related Topics

  Subject / Started by Replies Last post
0 Replies
127 Views
Last post December 16, 2008, 07:49:13 AM
by rpmolecule
0 Replies
50 Views
Last post April 19, 2009, 06:33:57 AM
by ralph0595
0 Replies
62 Views
Last post April 19, 2009, 06:37:06 AM
by ralph0595
0 Replies
61 Views
Last post April 19, 2009, 06:39:28 AM
by ralph0595
0 Replies
53 Views
Last post April 19, 2009, 06:41:37 AM
by ralph0595

Posting Disclaimer: Any individual may post a message in this forum and may do so anonymously. Therefore, the sole author is exclusively and entirely responsible for all opinions in that message. They do not represent the official opinions of Techronnati, its administrators or moderators or the Techronnati Management. Techronnati is merely acting as an impartial conduit for constitutionally protected free speech and is not responsible and will not be held liable for the content of such messages. All images and service logos are trademarks of their respective owners.