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.