NANVL

Purpose

This function determines whether the input value parameter n1 is NaN (not a number) and returns the result.

• If n1 is NaN, it returns the value of parameter n2.

• If n1 is not NaN, it returns the value of parameter n1.

  Notice

  This function applies only to the BINARY_FLOAT or BINARY_DOUBLE data types.

Syntax

NANVL(n1,n2)

Parameters

Return type

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

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

• If any parameter is NULL, the return value is NULL.

Examples

• Create a table named tbl1 and insert test data. If the value in column col2 is numeric, it 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 parameter is NULL, the return value is NULL.

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