Wednesday, March 07, 2012

An Empty String is Null in Oracle

(I ran this SQL in an Oracle 11 database.) Oracle treats an empty string as null. The length of an empty string is also null, not zero:
 
SQL> select nvl('','NULL1') null1 from dual;
 
NULL1
-----
NULL1
 
SQL> select nvl(to_char(length('')),'NULL2')
  2  null2 from dual;
 
NULL2
-----
NULL2
 
SQL>

This surprised me a little so I checked it a different way but got the same answer:
 
SQL> select 'Empty string is null'
  2  from dual
  3  where '' is null;
 
'EMPTYSTRINGISNULL'
--------------------
Empty string is null
 
SQL> select 'Empty string is not null'
  2  from dual
  3  where '' is not null;
 
no rows selected
 
SQL>

No comments: