nullif

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.