Oracle NANVL() function

Description

This function is used to return an alternative value n1 if the input value n2 is NaN (not a number), and returns n2 if n2 is not NaN. This function is particularly useful for handling and substituting NaN values in floating-point calculations for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE.
The function takes any numeric or nonnumeric data type (can be implicitly converted to a numeric data type) as an argument.
If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise, the function returns the same numeric data type as the argument

Uses of Oracle NANVL() Function

• Handling NaN values: Substitutes a specified value when the input is NaN.


• Data validation: Ensures that calculations and data manipulations do not produce NaN results.


• Improving data accuracy: Helps maintain data integrity by providing meaningful default values for NaN entries.


• Supporting floating-point operations: Useful in applications dealing with floating-point arithmetic, ensuring robust data processing.


• Enhanced querying: Assists in creating more reliable and predictable SQL queries by managing NaN values effectively.

Syntax :

NANVL(n2, n1)

Example:

We have a sample table float_point_test with three columns dec_num type 'NUMBER(10,2)', bin_double type 'BINARY_DOUB LE' and bin_float type 'BINARY_FLOAT'. Here is the table.

SQL> SELECT * FROM float_point_test;

   DEC_NUM BIN_DOUBLE  BIN_FLOAT
---------- ---------- ----------
   1513.67 1.514E+003 1.514E+003

Now insert a row into the float_point_test table and brows the table. Here is the command below.

SQL> INSERT INTO float_point_test  VALUES (0,'NaN','NaN');

1 row created.

SQL> SELECT * FROM float_point_test;

   DEC_NUM BIN_DOUBLE  BIN_FLOAT
---------- ---------- ----------
   1513.67 1.514E+003 1.514E+003
         0        Nan        Nan

The example below returns bin_float if it is a number. Otherwise, 0 is returned.

SELECT bin_float, NANVL(bin_float,0) 
FROM float_point_test;

Here is the result.

 BIN_FLOAT NANVL(BIN_FLOAT,0)
---------- ------------------
1.514E+003         1.514E+003
       Nan                  0

Previous: MOD
Next: POWER