In Oracle, the NANVL() function allows us to deal with NaN values by specifying another number to return in its place.

The way it works is, it accepts two arguments. If the first argument is NaN (not a number), it returns the second argument. If the first argument is a number, then it simply returns that number.

Note that this function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE.

Syntax

The syntax goes like this:

NANVL(n2, n1)

Each argument can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.

Example

We can produce NaN by dividing a zero float/double value by zero:

SELECT 0f/0 
FROM DUAL;

Result:

   0F/0 
_______ 
NaN     

But if we don’t want NaN to be returned, we can use the NANVL() function to return a different value:

SELECT NANVL(0f/0, 0) 
FROM DUAL;

Result:

   NANVL(0F/0,0) 
________________ 
0.0             

Here it is again, but this time we specify a different value to return in place of NaN:

SELECT NANVL(0f/0, 123) 
FROM DUAL;

Result:

   NANVL(0F/0,123) 
__________________ 
123.0              

NaN Floating-Point Literal

Oracle also provides some floating-point literals for situations that cannot be expressed as a numeric literal. These include binary_float_nan which represents a value of type BINARY_FLOAT for which the condition IS NAN is true, and binary_double_nan, which represents a value of type BINARY_DOUBLE for which the condition IS NAN is true.

Here’s an example of using these floating-point literals instead:

SELECT 
    NANVL(binary_double_nan, 0), 
    NANVL(binary_float_nan, 0)
FROM DUAL;

Result:

   NANVL(BINARY_DOUBLE_NAN,0)    NANVL(BINARY_FLOAT_NAN,0) 
_____________________________ ____________________________ 
0.0                           0.0                         

Passing a Number

As mentioned, if the first argument is a number, then it returns that number:

SELECT NANVL(33, 0) 
FROM DUAL;

Result:

   NANVL(33,0) 
______________ 
            33 

Non-Numeric Arguments

The arguments can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.

Here’s an example of what happens when the arguments don’t satisfy that criteria:

SELECT NANVL('Gosh', 'Dang')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NANVL('Gosh', 'Dang')
FROM DUAL
Error report -
ORA-01722: invalid number

Null Arguments

NANVL() returns null if any argument is null:

SET NULL 'null';

SELECT 
    NANVL(null, 16),
    NANVL(1024, null),
    NANVL(null, null)
FROM DUAL;

Result:

   NANVL(NULL,16)    NANVL(1024,NULL)    NANVL(NULL,NULL) 
_________________ ___________________ ___________________ 
             null                null                null 

By default, SQLcl and SQL*Plus return a blank space whenever a null value occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Missing Arguments

Calling NANVL() without any arguments results in an error:

SELECT NANVL()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NANVL()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action:

And the same thing applies when calling it with too many arguments:

SELECT NANVL(10, 2, 3)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NANVL(10, 2, 3)
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: