NANVL

Purpose

You can call this function to determine whether the input value n1 is not a number (NaN) and return the result.

• If n1 is NaN, the function returns n2.

• If n1 is not NaN, the function returns n1.

Notice

This function applies only for the BINARY_FLOAT and BINARY_DOUBLE data types.

Syntax

NANVL(n1,n2)

Parameters

Return type

• If any of the parameters is of the BINARY_DOUBLE data type, the return type is BINARY_DOUBLE.

• If any of the parameters is of the BINARY_FLOAT data type, the return type is BINARY_FLOAT.

• If any of the parameters is null, the function returns NULL.

Examples

• The following example creates table tbl1 and inserts test data into the table. If column col2 is a number, the function returns the original value. Otherwise, it returns 0.

  obclient> CREATE TABLE tbl1 (col1 NUMBER(10,2), col2 BINARY_DOUBLE, col3 BINARY_FLOAT);
  Query OK, 0 rows affected
  
  obclient> INSERT INTO tbl1 VALUES(1,1,1),(2,'NaN',2),(3,3,'NaN');
  Query OK, 3 rows affected
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM tbl1;
  +------+----------+----------+
   COL1  COL2      COL3     
  +------+----------+----------+
      1  1.0E+000  1.0E+000 
      2       Nan  2.0E+000 
      3  3.0E+000       Nan 
  +------+----------+----------+
  3 rows in set
  
  obclient> SELECT col2,NANVL(col2,0) FROM tbl1;
  +----------+---------------+
   COL2      NANVL(COL2,0) 
  +----------+---------------+
   1.0E+000       1.0E+000 
        Nan              0 
   3.0E+000       3.0E+000 
  +----------+---------------+
  3 rows in set
  

• If any of the parameters is null, the function returns NULL.

  obclient> SELECT NANVL('',1),NANVL(2,'') FROM DUAL;
  +-------------+-------------+
   NANVL('',1)  NANVL(2,'') 
  +-------------+-------------+
          NULL         NULL 
  +-------------+-------------+
  1 row in set