Originally posted by MES
Why does Oracle treat empty strings as NULL values?
It is one of Oracle's shortcomings and contrary to ANSI standards. The reason is to do with the way Oracle physically stores the data for strings, which is as a variable length string of characters and a character count. For the empty string, all it stores is the character count of zero. But Oracle also uses that character count to indicate whether a column is NULL (0) or not null (>0). So it cannot distinguish between '' and NULL.
This is a historic mistake, which I guess Oracle either thinks is too difficult to fix, or not worth fixing.
If a column in a row has no value, then the column is said to be null, or to contain a
null. Nulls can appear in columns of any datatype that are not restricted by NOT
NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not
known or when a value would not be meaningful.
Do not use null to represent a value of zero, because they are not equivalent. (Oracle
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.) Any arithmetic expression containing a null
always evaluates to null. For example, null added to 10 is null. In fact, all operators
(except concatenation) return null when given a null operand.
Originally posted by pmscontact
Oracle Help says :
(Oracle 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.) A
That's great! And how does Oracle Help suggest you go about treating them differently when there is no way to distinguish between them within Oracle? I mean, this code doesn't even work in Oracle: