Use nullif to find 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:
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)
The Psychology of Computing is stored in the table as “UNDECIDED,” but the query returns it as type NULL.
