Categories:

Conditional Expression Functions

# NVL2¶

Returns values depending on whether the first input is NULL:

• If expr1 is NOT NULL, then NVL2 returns expr2.

• If expr1 is NULL, then NVL2 returns expr3.

## Syntax¶

NVL2( <expr1> , <expr2> , <expr3> )


## Arguments¶

expr1

The expression to be checked to see whether it’s NULL.

expr2

If expr1 is not NULL, this expression will be evaluated and its value will be returned.

expr3

If expr1 is NULL, this expression will be evaluated and its value will be returned.

## Usage Notes¶

• All three expressions should have the same (or compatible) data type.

## Collation Details¶

• The collation specification for expr1 is ignored, since all that matters about this expression is whether it is NULL or not.

• The collation specifications for expr2 and expr3 must be compatible.

• The value returned from the function is the highest-precedence collation of expr2 and expr3.

## Examples¶

If a is not null, then return b, else return c:

SELECT a, b, c, NVL2(a, b, c) FROM i2;

--------+--------+--------+---------------+
A    |   B    |   C    | NVL2(A, B, C) |
--------+--------+--------+---------------+
0      | 5      | 3      | 5             |
0      | 5      | [NULL] | 5             |
0      | [NULL] | 3      | [NULL]        |
0      | [NULL] | [NULL] | [NULL]        |
[NULL] | 5      | 3      | 3             |
[NULL] | 5      | [NULL] | [NULL]        |
[NULL] | [NULL] | 3      | 3             |
[NULL] | [NULL] | [NULL] | [NULL]        |
--------+--------+--------+---------------+