Oracle NANVL() Function

Oracle NANVL() is a built-in function that allows you to provide a default value for parameters that have a value of NaN. It is only applicable to BINARY_FLOAT and BINARY_DOUBLE data types.

Oracle NANVL() Syntax

Here is the syntax for the Oracle NANVL() function:

NANVL(num1, num2)

Parameters

num1

Required.

num2

Required.

num1 and num2 can be any numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type.

Return Value

If num1 is NaN, the Oracle NANVL(num1, num2) function returns num2; otherwise, it returns num1.

If either parameter is NULL, NANVL() will return NULL.

Oracle NANVL() Examples

Here are some examples that demonstrate the usage of the Oracle NANVL() function.

Basic Usage

SELECT
    NANVL(1, 2)
FROM dual;

Output:

   NANVL(1,2)
_____________
            1

NaN

You can use BINARY_FLOAT_NAN and BINARY_DOUBLE_NAN as NaN:

SELECT
    NANVL(BINARY_FLOAT_NAN, 1),
    NANVL(BINARY_DOUBLE_NAN, 1)
FROM dual;

Output:

NANVL(BINARY_FLOAT_NAN,1)    NANVL(BINARY_DOUBLE_NAN,1)
____________________________ _____________________________
1.0                          1.0

Alternatively, you can generate a NaN using 0F/0:

SELECT
    NANVL(0F/0, 1)
FROM dual;

Output:

NANVL(0F/0,1)
________________
1.0

NULL Parameters

If either parameter is NULL, NANVL() will return NULL.

SET NULL 'NULL';
SELECT
    NANVL(1, NULL),
    NANVL(NULL, 1),
    NANVL(NULL, NULL)
FROM dual;

Output:

   NANVL(1,NULL)    NANVL(NULL,1)    NANVL(NULL,NULL)
________________ ________________ ___________________
            NULL             NULL                NULL

In this example, we used the SET NULL 'NULL'; statement to display NULL values as the string 'NULL'.

Conclusion

Oracle NANVL() is a built-in function that allows you to provide a default value for parameters that have a value of NaN. It is only applicable to BINARY_FLOAT and BINARY_DOUBLE data types.