Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function

SQL NANVL Function


SQL NANVL function return the alternative value if the specified value is NaN (Not a number). If value not NaN then return original value.

Value is a BINARY_FLOAT or BINARY_DOUBLE data type floating numbers.

Supported Oracle SQL Version :

  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Syntax :

NANVL(value,alternative_value)

Example :

SQL> CREATE TABLE nanvl_fun(
    bin_value BINARY_FLOAT
);

Table Created.

SQL> INSERT INTO nanvl_fun VALUES (0.7484522224);

1 row created.

SQL> INSERT INTO nanvl_fun VALUES (0.4514444555);

1 row created.

SQL> INSERT INTO nanvl_fun VALUES ('NaN');

1 row created.

SQL> SELECT * FROM nanvl_fun;

 BIN_VALUE
----------
7.485E-001
4.514E-001
       Nan

SQL> SELECT bin_value, NANVL(bin_value,'0') FROM nanvl_fun;

 BIN_VALUE NANVL(BIN_VALUE,'0')
---------- --------------------
7.485E-001           7.485E-001
4.514E-001           4.514E-001
       Nan                    0

Run it...