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