Search code examples
sqloracle-databasenullnvl

Oracle NVL with empty string

I have this table where NULL is the NULL value, not the string NULL:

MYCOL
--------
NULL
example

Why does this query not return the NULL row?

select * from example_so where nvl(mycol, '') = '';
Solution

  • '' is again NULL in Oracle, because Oracle doesn't support empty Strings just like Other High Level languages or DBMS..

    You need to look for NULL/empty string using IS NULL or IS NOT NULL

    No other relational operator work against NULL, though it is syntactically valid. SQLFiddle Demo

    It has to be,

    select * from example_so where mycol IS NULL

    EDIT: As per Docs

    Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.