NANVL

NANVL is a SQL function that is designed to replace a NAN (Not A Number) value with a specified expression especially in floating-point numbers. Perfect for handling and managing data integrity when dealing with ambiguous or undefined numerical data.

NANVL(number value, value if NaN)

  • number value: This parameter specifies the input number that is to be checked. It could be a variable, a numeric literal, or the result of another function or operation.
  • value if nan: This is a fallback value that NaNVL will return if the first parameter is not a number (NaN). This ensures that NaNVL always returns a valid number.

Example

SELECT NANVL(1.7, 3.2) as value_1, NANVL(NAN(), 4.7) as value_2 FROM dual;

Output

| VALUE_1 | VALUE_2 |
|---------|---------|
| 1.7     | 4.7     |

Explanation

In the Example, the Oracle function NANVL is used to replace a NaN value, if present, with a specific number. NANVL(1.7, 3.2) returned 1.7 because 1.7 is not NaN. NANVL(NAN(), 4.7) returned 4.7 because NaN was replaced with 4.7.

For in-depth explanations and examples SQL keywords where you write your SQL, install our extension.