CODEWITHSUNDEEP
sqloracle-database
KrGk
KrGk

Reputation: 71

Sql function - NANVL - different behaviour

Query1:

SQL> select nanvl(to_char(2),0) a from dual;

     A
    ---
     2

Query2:

SQL> select nanvl(to_char(2),null) a from dual;

     A
   -----

Doubt: Why the first query returns 2? I was expecting 0 as in query 2. I am confused . Please explain for me to understand.

Upvotes: 7

Views: 544

Answers (1)

Tim Child
Tim Child

Reputation: 3012

select nanvl(to_char(2),null) a from dual;

is equivalent to

select nanvl('2'),null) a from dual;

The literal value '2' is implicitly type converted into the floating point value 2, so nanvl() returns 2

try 

select nanvl('NaN'),0) a from dual;

that will give you 0

Upvotes: 1

Related Questions