NVL Function

If expr1 is NULL, expr2 is returned; otherwise, expr1 is returned.

Syntax

NVL(expr1, expr2)

Arguments

expr1 and expr2: Values of any data type.

expr2 must have the same data type as expr1.

Usage Notes

• The data type of the return value corresponds to the data type of expr2.
• If expr1 is of character data type, expr2 is converted to the data type of expr1 before comparison. If conversion is not possible, an error is returned. The return value has the data type STRING.
• If expr1 is numeric, the argument with the highest numeric priority is determined first. expr2 is then converted to this data type. The return value will have this data type.

Examples

SELECT col1 AS col1, 
       col2 AS col2, 
       NVL(col1, col2) AS result
FROM   (SELECT 1 AS col1, 1 AS col2
        UNION ALL 
        SELECT NULL AS col1, 2 AS col2
        UNION ALL 
        SELECT 1 AS col1, NULL AS col2        
        UNION ALL 
        SELECT NULL AS col1, NULL AS col2);

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

SELECT col1 AS col1, 
       col2 AS col2, 
       NVL(col1, col2) AS result
FROM   (SELECT 'a' AS col1, 'b' AS col2
        UNION ALL 
        SELECT NULL AS col1, 'b' AS col2
        UNION ALL 
        SELECT 'a' AS col1, NULL AS col2);

+------+------+--------+
| COL1 | COL2 | RESULT |
+------+------+--------+
| a    | b    | a      |
| NULL | b    | b      |
| a    | NULL | a      |
+------+------+--------+

SELECT col1 AS col1, 
       col2 AS col2, 
       NVL(col1, col2) AS result
FROM   (SELECT '1.1' AS col1, 1 AS col2 
        UNION ALL 
        SELECT NULL AS col1, 1 AS col2
        UNION ALL 
        SELECT '2' AS col1, NULL AS col2);

+------+------+--------+
| COL1 | COL2 | RESULT |
+------+------+--------+
|  1.1 | 1    | 1.1    |
| NULL | 1    | 1      |
|    2 | NULL | 2      |
+------+------+--------+