NANVL

Purpose

NANVL() determines whether the input value n1 is not a number (NaN) and returns 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

• Create the tbl1 table and insert 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