nullif compares two values; if the values are equal, nullif returns a null value. If the two values are not equal, nullif returns the value of the first value. This is useful for finding any missing, unknown, or inapplicable information that is stored in an encoded form. For example, values that are unknown are sometimes historically stored as -1. Using nullif, you can replace the -1 values with null and get the null behavior defined by Transact-SQL. The syntax is:
nullif(value1, value2)
If value1 equals value2, nullif returns NULL. If value1 does not equal value2, nullif returns value1. value1 and value2 are expressions, and their datatypes must be comparable
When you use nullif, Adaptive Server translates it internally to the following format:
case when value1 = value2 then NULL else value1 end
For example, the titles table uses the value “UNDECIDED” to represent books whose type category is not yet determined. The following query performs a search on the titles table for book types; any book whose type is “UNDECIDED” is returned as type NULL (the following output is reformatted for display purposes):
select title, "type"= nullif(type, "UNDECIDED") from titles
title type ----- -------- The Busy Executive’s Database Guide business Cooking with Computers: Surreptiti business You Can Combat Computer Stress! business . . . The Psychology of Computer Cooking NULL Fifty Years in Buckingham Palace K trad_cook Sushi, Anyone? trad_cook (18 rows affected)
Notice that The Psychology of Computing is stored in the table as “UNDECIDED”, but the query returns it as type NULL.
