Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

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 useful only 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

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