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>

Counting NULL Values

You should not use = NULL to check if something is NULL. You should use IS NULL instead. First find a table or view which has a column containing some null values and use the NVL function to count them:

SQL> select count(*) from dba_tab_comments
  2  where nvl(comments,'NULL') = 'NULL'
  3  /

  COUNT(*)
----------
      2354

SQL>

Count them again using IS NULL. The answer will be the same:

SQL> select count(*) from dba_tab_comments
  2  where comments is null
  3  /

  COUNT(*)
----------
      2354

SQL>

Finally, count them using = NULL. This will not find the null values:

SQL> select count(*) from dba_tab_comments
  2  where comments = null
  3  /

  COUNT(*)
----------
         0

SQL>