NVL2 Function

Returns

• expr2 if expr1 is not NULL,
• expr3 if expr1 is NULL.

Syntax

NVL2(expr1, expr2, expr3)

Arguments

expr1: Values of any data type.

---

expr2 and expr3: Values of any data type.

Usage Notes

• The data types of expr2 and expr3 must be compatible.
• If expr2 is textual, expr3 is converted to the data type of expr2 (exception: expr3 is NULL). The return value will have the data type STRING in the character set of expr2.
• If expr2 is numeric, the argument with the highest numeric priority is determined first. expr3 is then converted to this data type. The return value will have this data type.

Examples

SELECT col1 AS col1, 
       col2 AS col2, 
       col3 AS col3, 
       NVL2(col1, col2, col3) AS result
FROM   (SELECT 1 AS col1, '2' AS col2, 3 AS col3
        UNION ALL 
        SELECT NULL AS col1, '2' AS col2, 3 AS col3
        UNION ALL 
        SELECT 1 AS col1, NULL AS col2, 3 AS col3    
        UNION ALL 
        SELECT NULL AS col1, NULL AS col2, NULL AS col3);

+------+------+------+--------+
| COL1 | COL2 | COL3 | RESULT |
+------+------+------+--------+
| 1    | 2    | 3    | 2      |
| NULL | 2    | 3    | 3      |
| 1    | NULL | 3    | NULL   |
| NULL | NULL | NULL | NULL   |
+------+------+------+--------+

SELECT col1 AS col1, 
       col2 AS col2, 
       col3 AS col3, 
       NVL2(col1, col2, col3) AS result
FROM   (SELECT 1 AS col1, '2' AS col2, '3' AS col3
        UNION ALL 
        SELECT NULL AS col1, '2' AS col2, 'a' AS col3
        UNION ALL 
        SELECT 1 AS col1, NULL AS col2, '3' AS col3    
        UNION ALL 
        SELECT NULL AS col1, NULL AS col2, NULL AS col3);

+------+------+------+--------+
| COL1 | COL2 | COL3 | RESULT |
+------+------+------+--------+
| 1    | 2    | 3    | 2      |
| NULL | 2    | a    | a      |
| 1    | NULL | 3    | NULL   |
| NULL | NULL | NULL | NULL   |
+------+------+------+--------+