Oracle NANVL Function: A Simple Guide

The NANVL function in Oracle SQL is a specialized function used to handle NaN (Not a Number) values. NaN is a special marker used in floating-point arithmetic (specifically BINARY_FLOAT and BINARY_DOUBLE data types) to represent undefined or unrepresentable results, like the result of 0/0.

The NANVL function checks if a value is NaN and, if it is, returns an alternative, "normal" value that you specify.

What is the NANVL Function in Oracle?

The NANVL(n2, n1) function answers the question: "Is the value n2 equal to NaN? If it is, give me n1 instead. Otherwise, just give me n2."

This is very similar to COALESCE or NVL, but it is specifically designed to check for the NaN state, not for NULL.

NANVL Function Syntax

The syntax for NANVL requires two arguments:

NANVL(n2, n1)

Let's break that down:

• n2 (the value to check): This is the input value, typically a BINARY_FLOAT or BINARY_DOUBLE column or value, which might be NaN.
• n1 (the replacement value): This is the value Oracle will return if n2 is NaN.

Oracle NANVL Function Examples

Here are two practical examples of how to use NANVL.

Example 1: Replacing 'NaN' with Zero

This example shows how to replace a NaN value with 0. We use TO_BINARY_FLOAT('NaN') to generate a NaN value for the test.

Query:

SELECT 
  NANVL(TO_BINARY_FLOAT('NaN'), 0) AS "Cleaned_Value"
FROM DUAL;

Result:

Cleaned_Value
-------------
            0

Because the input was 'NaN', the function returned the second argument, 0.

Example 2: Handling a Normal Number

This example shows what happens when the input value is not NaN. The function simply returns the original number.

Query:

SELECT 
  NANVL(TO_BINARY_FLOAT(123.45), 0) AS "Cleaned_Value"
FROM DUAL;

Result:

Cleaned_Value
-------------
   1.2345E+02

(Which is scientific notation for 123.45)

Because the input 123.45 was a valid number (not 'NaN'), the function returned the first argument.