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
- Oracle 18c
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