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.