Oracle NANVL function

Preamble

The Oracle/PLSQL NANVL function allows you to replace a value for a floating point number such as BINARY_FLOAT or BINARY_DOUBLE when Nan (Not a number) is encountered. This is most often used to convert the value Nan (Not a number) to NULL or 0.

Oracle PLSQL syntax of the NANVL function

NANVL( value, replace_with )

Parameters or arguments

• value takes BINARY_FLOAT or BINARY_NUMBER to check for Nan (Not a number).
• replace_with this value is returned if the value is Nan (Not a number).

The NANVL function returns a substitute value.

The NANVL function can be used in the following versions of Oracle/PLSQL

The LNNVL function can be used in Oracle PLSQL.

For example:

SELECT NANVL(binary1, 0)
FROM test_table_id;

The above SQL query will return 0 if the binary1 field contains a Nan value (Not a number). Otherwise, it will return a value of binary1.

Another example of the NANVL function in Oracle/PLSQL:

SELECT NANVL(binary1, NULL)
FROM test_table_id;

The above SQL query will return NULL if the binary1 field contains a Nan value (Not a number). Otherwise, it will return a value of binary1.

Introduction to PLSQL functions in Oracle Database

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​