NVL2

​

Syntax

​

Scalar

nvl2(value, not_null_value, null_value)

• All result alternatives must have compatible types. Matching types are preserved; mixed types may be converted to a common type. Use explicit casts to choose the result type.

​

Arguments

​

Returns

• Type: Common SQL type after conversion of not_null_value and null_value
• NULL behavior: The NULL status of value selects a branch; a NULL selected branch produces NULL.

​

Properties

​

Usage notes

• The first argument is tested but does not determine the type of the returned alternatives.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.
• Deterministic: The same inputs and settings produce the same result.

​

Examples

​

Typical behavior

SELECT nvl2(CAST(NULL AS INT), 2, 1) AS "value", nvl2(9, 2, 1) AS present;

​

Choose a label for a missing string

SELECT nvl2(CAST(NULL AS VARCHAR), 'present', 'missing') AS null_input, nvl2('', 'present', 'missing') AS empty_input;

​

Related functions

• nvl
• if