Why are empty VARCHAR2S always NULL?

From: Wolfram Roesler <wr_at_grp.de>
Date: Wed, 21 Aug 2002 11:25:00 +0200
Message-ID: <Xns927174CE4539Fwrgrpde@62.153.159.134>

Hello,

does anyone know why Oracle stores empty VARCHAR2 strings as NULL? That is, (assuming the table has exactly one row,)

UPDATE table SET colname='';
SELECT * FROM table WHERE colname='';
(nothing found)

SELECT * FROM table WHERE colname IS NULL;
(row is found)

In my understanding of the logic behind NULL, it should be a value distinct from an empty string. Is this a bug in Oracle? If so, when will it be fixed? Do I have to use NVL(colname,'') all over to prevent the fix from breaking my code?

I found a thread on Google where this was discussed in 1999 (search for subject "differences between null and empty varchar2"); does anyone have more up-to-date information?

Thanks for your help
W. Roesler Received on Wed Aug 21 2002 - 04:25:00 CDT