Functions related to NULL values - PolarDB - Alibaba Cloud Documentation Center

This topic describes the syntax, parameters, and examples of functions that are related to NULL values.

NANVL

Description

This function is used to determine whether the value of the n1 parameter is not a number (NaN) and returns the result.

If the value of the n1 parameter is NaN, the function returns the value of the n2 parameter.
If the value of the n1 parameter is a number, the function returns the value of the n1 parameter.

Syntax

NANVL(n1, n2)

Parameters

Parameter	Description
n1	The value of this parameter supports floating-point numbers only of the `BINARY_FLOAT` and `BINARY_DOUBLE` data types.
n2	The value of this parameter supports floating-point numbers only of the `BINARY_FLOAT` and `BINARY_DOUBLE` data types.

Return value

The return value is of the BINARY_FLOAT or BINARY_DOUBLE data type.

Example

CREATE TABLE float_point_demo (bf BINARY_FLOAT,bd BINARY_DOUBLE);
INSERT INTO float_point_demo VALUES (123.456,'NaN');
INSERT INTO float_point_demo VALUES ('NAN',123.456);

SELECT * FROM float_point_demo;
   bf    |   bd    
---------+---------
 123.456 |     NaN
     NaN | 123.456

SELECT NANVL(bf,0) "nanvl_1", NANVL(bd,0) "nanvl_2" FROM float_point_demo;
 nanvl_1 | nanvl_2 
---------+---------
 123.456 |       0
       0 | 123.456

NVL2

Description

This function determines the value to be returned based on whether the specified expression is NULL.

If the value of the expr1 parameter is not NULL, the NVL2 function returns the value of the expr2 parameter.
If the value of the expr1 parameter is NULL, the NVL2 function returns the value of the expr3 parameter.

Syntax

NVL2(expr1, expr2, expr3)

Parameters

Parameter	Description
expr1	The value of this parameter can be of any data type.
expr2	The value of this parameter is of a data type other than `LONG`.
expr3	The value of this parameter is of a data type other than `LONG`.

Return value

If the values of the expr2 and expr3 parameters are of the same data type, a value of the same data type is returned.
If the values of the expr2 and expr3 parameters are of different data types, the data type of the return value is determined by the following conditions:
- If the value of the expr2 parameter is character data, the database converts the data type of the expr3 parameter to that of the expr2 parameter before comparison, unless the value of the expr3 parameter is a null constant. In this case, data type conversion is not required. A value of the VARCHAR2 data type is returned in the character set of the expr2 parameter.
- If the value of the expr2 parameter is a number, the database determines which parameter has the highest numeric precedence, implicitly converts the data type of the other parameter to that of the parameter that has the highest numeric precedence, and then returns a value of the converted data type.

Example

SELECT nvl2('A'::text, 'B', 'C') FROM DUAL;
 nvl2 
------
 B

SELECT nvl2(NULL, 2, 3) FROM DUAL;
 nvl2 
------
    3

NVL

Description

This function replaces NULL values in the query result with strings.

If the value of the expr1 parameter is NULL, the NVL function returns the value of the expr2 parameter.
If the value of the expr1 parameter is not NULL, the NVL function returns the value of the expr1 parameter.

Syntax

NVL(expr1, expr2)

Parameters

Parameter	Description
expr1	The value of this parameter can be of any data type.
expr2	The value of this parameter can be of any data type.

Return value

The values of the expr1 and expr2 parameters can be of any data type.

If their data types are different, the database implicitly converts one to the other. If the data type cannot be implicitly converted, the database returns an error.

Implicit data type conversion is implemented based on the following conditions:

If the value of the expr1 parameter is character data, the database converts the data type of the expr2 parameter to that of the expr1 parameter before comparison and returns a value of the VARCHAR2 data type in the character set of the expr1 parameter.

If the value of the expr1 parameter is a number, the database determines which parameter has the highest numeric precedence, implicitly converts the data type of the other parameter to that of the parameter that has the highest numeric precedence, and then returns a value of the converted data type.

Example

SELECT nvl(NULL::text, 'B') FROM DUAL;
 nvl 
-----
 B

NULLIF

Description

The NULLIF function compares the values of the expr1 and expr2 parameters.

Syntax

NULLIF(expr1, expr2)

Parameters

Parameter	Description
expr1	The value of this parameter can be of any data type.
expr2	The value of this parameter can be of any data type.

Return value

If the values of the expr1 and expr2 parameters are the same, the function returns NULL.
If the values of the expr1 and expr2 parameters are different, the function returns the value of the expr1 parameter.

Example

SELECT nullif('a','') FROM DUAL;
 nullif 
--------
 a

select nullif('','') FROM DAUL;
 nullif 
--------

LNNVL

Description

This function is used to provide a concise way to evaluate a condition when one or two operands are null.

Note

This function can be used as long as scalar expressions occur, even in cases where the IS[NOT]NULL, AND, or OR condition is invalid but potential NULL operands need to be considered. A database sometimes may internally use the LNNVL function to rewrite the NOT IN condition to the NOT EXISTS condition. In this case, the output of EXPLAIN PLAN shows this action in the schedule output. Scalar values can be calculated by conditions except complex conditions that include the AND, OR, or BETWEEN condition.

Syntax

LNNVL(condition)

Parameters

Parameter	Description
condition	The input condition.

Return value

This function can be used only in the WHERE clause of a query, and it uses a condition as a parameter.

If the condition is FALSE or UNKNOWN, TRUE is returned.
If the condition is TRUE, FALSE is returned.

Example

create table lnnvltbl(name varchar(10), id int);
insert into lnnvltbl values(null, 1); 
insert into lnnvltbl values('', 2);
insert into lnnvltbl values ('null',3);
insert into lnnvltbl values('s', 4);
insert into lnnvltbl values ('ss',5);
select * from lnnvltbl where lnnvl(name is not null) order by id;
 name | id 
------+----
      |  1
      |  2

COALESCE

Description

This function returns the first non-NULL expression from a list of expressions. At least one expression cannot be NULL. If the values of all expr parameters are NULL, the function returns NULL.

A database uses short circuit evaluation. In other words, the database evaluates the value of each expr parameter and determines whether the value is NULL, rather than evaluating the values of all expr parameters before one of the parameter values is determined as NULL.

Syntax

COALESCE(expr [, expr]...)

Parameters

Parameter	Description
expr	The value of this parameter is a value of the numeric or character data type or an expression.

Return value

If the values of all expr parameters are of the numeric data type or a non-numeric data type that can be implicitly converted to the numeric data type, the database determines which parameter has the highest numeric precedence, implicitly converts the data types of the other parameters to the data type of the parameter that has the highest numeric precedence, and then returns a value of the converted data type.

Example

SELECT coalesce(null,3.934,1) FROM DUAL;
 coalesce 
----------
    3.934

select coalesce(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,1) FROM DUAL;
 coalesce 
----------
        1