As an Oracle newbie, I have been very curious and just discovered that Oracle treats zero-length empty string as NULL.
SQL> SELECT (
2 CASE
3 WHEN '' IS NULL THEN 'null'
4 ELSE 'not null'
5 END
6 ) "IS NULL?"
7 FROM dual;
8 /
IS NULL?
--------
null
I know that Sybase ASE, MS SQL Server and MySQL all return 'not null' from the query above. So, why does Oracle treat empty string as NULL?
This is not pure academic question since the treatment of empty strings as NULLs has one very serious consequence: an application cannot distinguish between "touched" and "non-touched" columns. For example, the blank field in an application form that represents a person's mobile tel. number could mean:
(1) it is known fact that the person HAS NOT tel. number at all (the field is touched by empty string)
(2) the person's tel. number is UNKNOWN, NULL (the field is not touched)
Testing the underlying data in the database (NULL?/NOT NULL?) the application can mark the blank field with labels 'not exists' or 'unknown' accordingly. This distinction is not possible in Oracle since Oracle treats both NULL and '' as NULL.
I would appreciate if somebody could explain the reasons for this.
Regards
Albert