SQL Reference Guide

NULLIF (SQL-92 compatible)

Syntax

NULLIF ( expression1, expression2 )

Description

The NULLIF scalar function is a type of conditional expression (See Conditional Expressions for more information and 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, ' '));