NVL2

Purpose

This function returns different values based on whether the input expression expr1 is null.

• If expr1 is not null, it returns the value of expr2.

• If expr1 is null, it returns the value of expr3.

Syntax

NVL2(expr1, expr2, expr3)

Parameters

Return type

• If expr1 is null, and expr3 is also null, it returns NULL. If expr3 is not null, the return type is described in the Considerations section below.

• If expr1 is not null, and expr2 is null, it returns NULL. If expr2 is not null, the return type is described in the Considerations section below.

  Considerations

  If the data types of expr2 and expr3 are different, the database implicitly converts one to the other. If the conversion is not possible, the database returns an error. If expr2 is a character or numeric type, the implicit conversion rules are as follows:

  • If expr2 is a character type, such as CHAR, NCHAR, NVARCHAR, VARCHAR2, or VARCHAR, OceanBase Database first converts expr3 to the data type of expr2 and then returns the value. If expr3 is NULL, no data type conversion is required, and the result is returned as a VARCHAR2 type.
  • If expr2 is a numeric type, such as NUMBER, FLOAT, BINARY_FLOAT, or BINARY_DOUBLE, OceanBase Database determines which parameter has the highest numeric precedence, implicitly converts the other parameter to that data type, and returns the result in that data type.

Examples

Create a table named tbl1 and insert five rows of data. Replace the null values in the col2 column with 100 and the non-null values 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