Purpose
This function returns a non-null value from two expressions. If both expr1 and expr2 evaluate to NULL, the NVL function returns NULL.
Syntax
NVL(expr1, expr2)
Parameters
| Parameter | Description |
|---|---|
| expr1 | The first parameter. The data type can be any built-in data type. |
| expr2 | The second parameter. The data type can be any built-in data type. |
Note
expr1 and expr2 must be of the same type or implicitly convertible to the same type. If they cannot be implicitly converted, the database returns an error. The implicit conversion rules are as follows:
- If
expr1is of a character type, such asCHAR,NCHAR,NVARCHAR,VARCHAR2, orVARCHAR, the database convertsexpr2to the data type ofexpr1before comparing them and returns aVARCHAR2value in the character set ofexpr1. - If
expr1is of a numeric type, such asNUMBER,FLOAT,BINARY_FLOAT, orBINARY_DOUBLE, the database determines which parameter has the highest numeric precedence, implicitly converts the other parameter to that data type, and returns the data type.
Return type
If both
expr1andexpr2areNULL,NULLis returned.If
expr1is of a character type, such asCHAR,NCHAR,NVARCHAR,VARCHAR2, orVARCHAR, aVARCHAR2value in the character set ofexpr1is returned.If
expr1is of a numeric type, such asNUMBER,FLOAT,BINARY_FLOAT, orBINARY_DOUBLE, the data type with the highest precedence is returned.
Examples
Create a table named tbl1 and insert five rows of data. Replace the NULL values in the col2 column with 100.
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,NVL(col2,100) FROM tbl1;
+------+---------------+
| COL1 | NVL(COL2,100) |
+------+---------------+
| 1 | 30 |
| 2 | 100 |
| 3 | 50 |
| 4 | 80 |
| 5 | 10 |
+------+---------------+
5 rows in set