4.9.2.56 NULLIF (SQL-92 compatible)

Syntax

NULLIF ( expression1, expression2 )

Description

The NULLIF scalar function is a type of conditional expression. (See the topic on Conditional Expressions for a summary of all the conditional expressions.)

The NULLIF scalar function returns a null value for expression1 if it is equal to expression2. It's useful for converting values to null from applications that use some other representation for missing or unknown data.

Notes

• This function is not allowed in a GROUP BY clause.
• Arguments to this function cannot be query expressions.
• The NULLIF expression is shorthand notation for a common case that can also be represented in a CASE expression, as follows:

CASE
    WHEN expression1 = expression2 THEN NULL
    ELSE expression1

Example

This example uses the NULLIF scalar function to insert a null value into an address column if the host-language variable contains a single space character.

INSERT INTO employee (add1) VALUES (NULLIF (:address1, ' '));