ISNULL(check-expression,replace-expression)
Arguments
| Argument |
Description |
| check-expression |
The expression to be evaluated. |
| replace-expression |
An expression that is returned if check-expression is NULL. |
ISNULL returns the same data type as check-expression.
Description
ISNULL 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 data type of replace-expression should be compatible with the data type of check-expression.
Note that the ISNULL function is the same as the NVL function, which is provided for Oracle compatibility.
Refer to NULL section of the “Language Elements” chapter of Using InterSystems SQL 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, such as 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 issues 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.
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 |
| ISNULL(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 |
| NVL(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
In the following example, the first ISNULL returns the second expression (99) because the first expression is NULL. The second ISNULL returns the first expression (33) because the first expression is not NULL:
SELECT ISNULL(NULL,99) AS IsNullT,ISNULL(33,99) AS IsNullF
The following example returns the string 'No Preference' if FavoriteColors is NULL; otherwise, it returns the value of FavoriteColors:
SELECT Name,
ISNULL(FavoriteColors,'No Preference') AS ColorChoice
FROM Sample.Person
Compare the behavior of ISNULL with IFNULL:
SELECT Name,
IFNULL(FavoriteColors,'No Preference') AS ColorChoice
FROM Sample.Person
See Also
