Empty String vs. NULL Value in Oracle

Today I was writing a query trying to eliminate NULL values and empty strings from a column. I found a strange behavior in Oracle that it considers NULL and empty strings ( ” ) equal which is not ANSI. Other database systems which are more compliant to ANSI like PostgreSQL differentiate between NULL and ( ” ) correctly.

Check this out:

SQL> create table tes (a varchar2(2));
Table created.
SQL> insert into tes values (''); -- inserted an empty string
1 row created.
SQL> select * from tes where a is not null;
no rows selected

Gives me no rows although it has a string with zero length in it.

SQL> select length(a) from tes;
 LENGTH(A)
----------

Even the length function shows a NULL it should have been saying “0″.

This brings another interesting scenario. What if you want to find out empty strings in your column?

SQL> select * from tes where a = '';

no rows selected

This does not bring in any rows because a NULL is not equal to NULL. The correct query would be:

SQL> select * from tes where trim(a) is null;
A
--

Now it brings in one row because the TRIM function returns null if no characters are left in the given string after trimming. A more ANSI compliant database would have returned an empty string ( ” ) though.