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.

Advertisements

One thought on “Empty String vs. NULL Value in Oracle

  1. I am looking for a way to tell Oracle to treat NULLs and empty strings different.

    SQL Server does have an option for this, but does Oracle have such an option to?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s