Oracle NVL with empty string

sql oracle null nvl
kmkaplan picture kmkaplan · Oct 9, 2014 · Viewed 37.5k times · Source

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, '') = '';

Answer

Maheswaran Ravisankar picture Maheswaran Ravisankar · Oct 9, 2014

'' 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.

Related questions

Update columns with Null values

I tried updating a table as follows: update userloginstats set logouttime = sysdate where logouttime = null; It didn't update the columns with null values. What went wrong?

Read More
Why does Oracle 9i treat an empty string as NULL?

I know that it does consider ' ' as NULL, but that doesn't do much to tell me why this is the case. As I understand the SQL specifications, ' ' is not the same as NULL -- one is …

Read More
SQL: AVG with NULL Values

as far as i understood the AVG() function ignores NULL Values. So AVG(4,4,4,4,4,NULL) --> 4 In my case i don´t want this to happen. I need a solution like that: AVG(4,4,4,4,4,NULL) --> 3,33 without replacing the NULL …

Read More