NANVL Function in Oracle SQL – PLSQL

Oracle SQL / PLSQL NANVL function allows us to substitute a value for a floating point number such a BINARY_FLOAT or BINARY_DOUBLE, when an SQL Not A Number (NAN) value is encountered.

The Oracle SQL NANVL function is most commonly used to convert Not A Number (NAN) values to either 0 or NULL.

Oracle SQL / PLSQL Syntax for the NANVL function is:

SELECT NANVL (value_1, replace_with)
FROM table_name;

  • value_1 is the BINARY_FLOAT or BONARY_NUMBER to be tested for a Not A Number (NAN)
  • the replace_with is the value that will be returned if value_1 is NAN.

Example of using Oracle SQL NANVL function

 SELECT NANVL(to_char(2),NULL)
FROM dual;

Will return NULL.


Tagged , , , . Bookmark the permalink.
  • krgk

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

    A

    ———-

    2

    Why is it returns 2 and not 0?

    • techhoney

      seems that NANVL function implicitly converts the 1st parameter to a number, hence when you’re passing to_char(2) then it converts that ‘2’ to a number and then returns the appropriate value.