CODEWITHSUNDEEP
sqloracle-databasenullnvl
kmkaplan
kmkaplan

Reputation: 18960

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

Upvotes: 14

Views: 51363

Answers (3)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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

Upvotes: 22

user3380585
user3380585

Reputation: 173

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

nvl(mycol, '') will be resulted as NULL and when you compared NULL with empty string it cant be compared

create table t(id varchar2(2));
insert into t values (nvl(null,''));   <------ this will insert NULL
insert into t values (nvl(null,'et'));

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

Because NULL = NULL is simply unknown. A third state perhaps? It is neither TRUE nor FALSE.

Oracle considers an EMPTY STRING as NULL.

nvl(mycol, '') makes no real sense, as you are making the NULL back to NULL and comparing it again with NULL.

SQL> WITH DATA AS(
  2  SELECT 1 val, 'NULL' str FROM dual UNION ALL
  3  SELECT 2, NULL str FROM dual UNION ALL
  4  SELECT 3, '' str FROM dual UNION ALL
  5  SELECT 4, 'some value' str FROM dual)
  6  SELECT val, NVL(str, 'THIS IS NULL') FROM data WHERE str IS NULL
  7  /

       VAL NVL(STR,'THI
---------- ------------
         2 THIS IS NULL
         3 THIS IS NULL

SQL>

Upvotes: 9

Related Questions