> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# NVL2

> Returns one value for a non-NULL input and another for a NULL input.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Scalar function</span></div>

The NULL status of value selects a branch; a NULL selected branch produces NULL.

## Syntax

### Scalar

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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

| Name | Type | Required | Description |
| - | - | - | - |
| value | BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT | Yes | Value whose NULL status is inspected. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |
| not\_null\_value | BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT | Yes | Value used when value is not NULL. Must convert to the same SQL type as the other arguments in this comparison or result branch. Use explicit CAST when mixing types. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |
| null\_value | BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT | Yes | Value used when value is NULL. Must convert to the same SQL type as the other arguments in this comparison or result branch. Use explicit CAST when mixing types. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |

## 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

| Property | Value |
| - | - |
| Deterministic | Yes |
| Supports window usage | No |
| Exact result | Partial (Selection is exact; result branches may be cast to a common type.) |

## 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

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT nvl2(CAST(NULL AS INT), 2, 1) AS "value", nvl2(9, 2, 1) AS present;
```

| value | present |
| - | - |
| 1 | 2 |

### Choose a label for a missing string

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT nvl2(CAST(NULL AS VARCHAR), 'present', 'missing') AS null_input, nvl2('', 'present', 'missing') AS empty_input;
```

| null\_input | empty\_input |
| - | - |
| missing | present |

## Related functions

* [nvl](/query/sql/functions/conditional/nvl)
* [if](/query/sql/functions/conditional/if)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.