Differences of empty string between Oracle and Informix

This article was originally posted in my csdn.net blog on 16 Feb 2009.


The behaviors of empty string '' (no any characters between two single quotation marks) in Oracle and Informix are much different. Great attention should be paid to avoid making mistakes.


In Oracle:
SQL> select * from dual where '' is null;
DUMMY
-----
X

This indicates '' is the same with null. Therefore it can’t be compared with other values or itself:
SQL> select * from dual where ''='';
DUMMY
-----

SQL> select * from dual where ''!='';
DUMMY
-----

That is to say, the result of nvl(col1,'')='' is false even when col1 is null.

And we can also make a conclusion, the result is false when empty string '' is comapred with a string containing whitespaces ' '(whatever how many spaces):
SQL> select * from dual where ''=' ';
DUMMY
-----


Now let’s have a look in Informix:

select count(*) from systables where ''='';
367

This indicates empty string can be compared with others. Therefore it is not the same with null.
According to above result, we can be convinced that nvl(col1,'')='' is true when either col1 is null or col1=''.

But because is null or is no null can only be used for a column , we can’t write a where statement like where '' is null .

Because in Informix empty string can be compared with others, so let’s look what happens when we compare it with the string containing whitespaces:

select count(*) from systables where ''=' ';
367

That’s to say, empty string is the same with string containing whitespaces。