Skip to main content

NVL (SQL)

A function that tests for NULL and returns the appropriate expression.

Synopsis

NVL(check-expression,replace-expression)

Description

NVL evaluates check-expression and returns one of two values:

  • If check-expression is NULL, replace-expression is returned.

  • If check-expression is not NULL, check-expression is returned.

The arguments check-expression and replace-expression can have any data type. If their data types are different, SQL converts replace-expression to the data type of check-expression before comparing them. The data type of the return value is always the same as the data type of check-expression, unless check-expression is character data, in which case the return value’s data type is VARCHAR2.

The collation of the resulting expression is determined by the first argument that is not of a collation type that may be coerced into the collation of the other. A collation that may be coerced into a different collation is called “coercible.” Possible scenarios are as follows:

  • The collations of check-expression and replace-expression are not coercible: The output collation is the collation of check-expression.

  • The collation of check-expression is not coercible, but replace-expression is coercible: The output collation is the collation of check-expression.

  • The collation of check-expression is coercible, but replace-expression is not coercible: The output collation is the collation of replace-expression.

  • The collations of check-expression and replace-expression are coercible: The output collation is coercible.

Note that NVL is supported for Oracle compatibility, and is the same as the ISNULL function.

Refer to NULL for further details on NULL handling.

DATE and TIME Display Conversion

Some check-expression data types require conversion from Logical mode to ODBC mode or Display mode. For example the DATE and TIME data types. If the replace-expression value is not the same data type, this value cannot be converted in ODBC mode or Display mode, and an SQLCODE error is generated: -146 for DATE data type; -147 for TIME data type. For example, ISNULL(DOB,'nodate') cannot be executed in ODBC mode or Display mode; it issue an SQLCODE -146 error with the %msg Error: 'nodate' is an invalid ODBC/JDBC Date value or Error: 'nodate' is an invalid DISPLAY Date value. To execute this statement in ODBC mode or Display mode, you must CAST the value as the appropriate data type: ISNULL(DOB,CAST('nodate' as DATE)). This results in a date 0, which displays as 1840-12-31.

Arguments

check-expression

The expression to be evaluated.

replace-expression

The expression that is returned if check-expression is NULL.

NVL returns the same data type as check-expression.

NULL Handling Functions Compared

The following table shows the various SQL comparison functions. Each function returns one value if the logical comparison tests True (A same as B) and another value if the logical comparison tests False (A not same as B). These functions allow you to perform NULL logical comparisons. You cannot specify NULL in an actual equality (or non-equality) condition comparison.

SQL Function Comparison Test Return Value
NVL(ex1,ex2) ex1 = NULL

True returns ex2

False returns ex1

IFNULL(ex1,ex2) [two-argument form] ex1 = NULL

True returns ex2

False returns NULL

IFNULL(ex1,ex2,ex3) [three-argument form] ex1 = NULL

True returns ex2

False returns ex3

{fn IFNULL(ex1,ex2)} ex1 = NULL

True returns ex2

False returns ex1

ISNULL(ex1,ex2) ex1 = NULL

True returns ex2

False returns ex1

NULLIF(ex1,ex2) ex1 = ex2

True returns NULL

False returns ex1

COALESCE(ex1,ex2,...) ex = NULL for each argument

True tests next ex argument. If all ex arguments are True (NULL), returns NULL.

False returns ex

Examples

This following example returns the replace-expression (99) because the check-expression is NULL:

SELECT NVL(NULL,99) AS NullTest

This following example returns the check-expression (33) because check-expression is not NULL:

SELECT NVL(33,99) AS NullTest

The following example returns the string 'No Preference' if FavoriteColors is NULL; otherwise, it returns the value of FavoriteColors:

SELECT Name, NVL(FavoriteColors,'No Preference') AS ColorChoice 
FROM Sample.Person

See Also

FeedbackOpens in a new tab