Oracle / PLSQL: NANVL Function
This Oracle tutorial explains how to use the Oracle/PLSQL NANVL function with syntax and examples.
Description
The Oracle/PLSQL NANVL function lets you substitute a value for a floating point number such as BINARY_FLOAT or BINARY_DOUBLE, when a Nan (Not a number) value is encountered. This is most commonly used to convert Nan (Not a number) values into either NULL or 0.
Syntax
The syntax for the NANVL function in Oracle/PLSQL is:
NANVL( value, replace_with )
Parameters or Arguments
- value
- The BINARY_FLOAT or BINARY_NUMBER to test for a Nan (Not a number).
- replace_with
- The value returned if value is Nan (not a number).
Returns
The NANVL function returns a substitute value.
Applies To
The NANVL function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g
Example
The NANVL function can be used in Oracle/PLSQL.
For example:
select NANVL(binary1, 0) from test_table;
The SQL statement above would return 0 if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value.
Another example of the NANVL function in Oracle/PLSQL is:
select NANVL(binary1, NULL) from test_table;
The SQL statement above would return NULL if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value.
Advertisements