NVL2

Purpose

NVL2() returns different values based on whether the input expression expr1 evaluates to NULL.

• If expr1 is not NULL, the function returns the value of expr2.

• If expr1 is NULL, the function returns the value of expr3.

Syntax

NVL2(expr1, expr2, expr3)

Parameters

Return type

• When expr1 is NULL, if expr3 is NULL as well, the function returns NULL. If expr3 is not NULL, the return type is as described in Note.

• When expr1 is not NULL, if expr2 is NULL, the function returns NULL. If expr2 is not NULL, the return type is as described in Note.

  Note

  If the data types of expr2 and expr3 are different, OceanBase Database implicitly converts one to the other. If their data types cannot be implicitly converted, the database returns an error. If expr2 is of a character or numeric data type, the implicit conversion is implemented in the following way:

  • If expr2 is of the CHAR, NCHAR, NVARCHAR, VARCHAR2 or VARCHAR data type, OceanBase Database converts expr3 to the data type of expr2 before returning the value. If expr3 is NULL, data type conversion is no implemented, and the return type is VARCHAR2.
  • If expr2 is of the NUMBER, FLOAT, BINARY_FLOAT or BINARY_DOUBL data type, OceanBase Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

Examples

Execute the following statement to create table tbl1 and insert five data entries into it. Replace NULLs in the col2 column with 100, and replace non-NULL values in the column with 50.

obclient> CREATE TABLE tbl1 (col1 INT,col2 INT);
Query OK, 0 rows affected

obclient> INSERT INTO tbl1 VALUES (1,30),(2,NULL),(3,50),(4,80),(5,10);
Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

obclient> SELECT col1,NVL2(col2,50,100) FROM tbl1;
+------+-------------------+
| COL1 | NVL2(COL2,50,100) |
+------+-------------------+
|    1 |                50 |
|    2 |               100 |
|    3 |                50 |
|    4 |                50 |
|    5 |                50 |
+------+-------------------+
5 rows in set