Purpose
NVL() returns a non-NULL value from two expressions. If the results of expr1 and expr2 are both 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 in OceanBase Database. |
| expr2 | The second parameter. The data type can be any built-in data type in OceanBase Database. |
Note
expr1 and expr2 must be of the same type, or they can be implicitly converted to the same type. If their data types cannot be implicitly converted, the database returns an error. The implicit conversion rules are as follows:
- If
expr1is of theCHAR,NCHAR,NVARCHAR,VARCHAR2orVARCHARdata type, OceanBase Database convertsexpr2to the data type ofexpr1before comparing it withexpr1and returnsVARCHAR2in the character set ofexpr1. - If
expr1is of theNUMBER,FLOAT,BINARY_FLOATorBINARY_DOUBLdata type, OceanBase Database determines which parameter has the highest numeric precedence, implicitly converts the other parameter to that data type, and returns that data type.
Return type
If
expr1andexpr2areNULL, OceanBase Database returnsNULL.If
expr1is of theCHAR,NCHAR,NVARCHAR,VARCHAR2, orVARCHARtype, OceanBase Database returns data of theVARCHAR2type in the character set ofexpr1.If
expr1is of theNUMBER,FLOAT,BINARY_FLOAT, orBINARY_DOUBLEdata type, OceanBase Database returns a data type with the highest numeric precedence in theexpr1data set.
Examples
Execute the following statement to create table tbl1 and insert five data entries into it. Replace NULLs 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