NVL

The NVL function returns a non-NULL value from two expressions. If the results of expr1 and expr2 are NULL values, the NVL function returns NULL.

Syntax

NVL(expr1, expr2)

Parameters

expr1 and expr2 must be the same type or can be implicitly converted to the same type. If they cannot be implicitly converted, ApsaraDB for OceanBase returns an error. Implicit conversions are implemented in the following ways:

• If expr1 is the data of the CHAR, NCHAR, NVARCHAR, VARCHAR2, or VARCHAR character type, ApsaraDB for OceanBase converts expr2 to the data type of expr1 before it compares expr1. Then, ApsaraDB for OceanBase returns VARCHAR2 in the character set of expr1.

• If expr1 is the data of the NUMBER, FLOAT, BINARY_FLOAT, or BINARY_DOUBLE numeric type, ApsaraDB for OceanBase determines the parameter that has the highest numeric precedence. ApsaraDB for OceanBase implicitly converts the other parameter to this data type and returns the data type.

Return type

If expr1 and expr2 are NULL, NULL is returned. If expr1 is the data of the CHAR, NCHAR, NVARCHAR, VARCHAR2, or VARCHAR character type, VARCHAR2 in the character set of expr1 is returned. If expr1 is the data of the NUMBER, FLOAT, BINARY_FLOAT, or BINARY_DOUBLE numeric type, the data type that has the highest numeric precedence in expr1 is returned.

Examples

Assume that data is inserted into the employee name column name and the commission column commission_pct in an EMPLOYEES table. Execute the following statements:

CREATE TABLE EMPLOYEES (name VARCHAR(20),commission_pct float(3));
INSERT INTO EMPLOYEEs VALUES ('Baer', null);
INSERT INTO EMPLOYEEs VALUES ('Bada', null);
INSERT INTO EMPLOYEEs VALUES ('Boll', 0.1);
INSERT INTO EMPLOYEEs VALUES ('Bates', 0.15);
INSERT INTO EMPLOYEEs VALUES ('Eric', null);

Query the name and the commission of an employee. If the employee does not receive the commission, Not Applicable appears. Execute the following statement:

SELECT name, NVL(TO_CHAR(commission_pct), 'Not Applicable') commission 
FROM employees WHERE name LIKE 'B%' ORDER BY name;

The following query result is returned:

+--------------+----------------+
|        NAME  |     COMMISSION |
+--------------+----------------+
|        Baer  | Not Applicable |
+--------------+----------------+
|        Bada  | Not Applicable |
+--------------+----------------+
|        Boll  |             .1 |
+--------------+----------------+
|       Bates  |            .15 |
+--------------+----------------+