Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - NULLIF() - Replacing Given Values with NULL

By: FYIcenter.com

(Continued from previous topic...)

How To Replace Given Values with NULL using NULLIF()?

Sometime you want to hide certain values by replacing them with NULL values. SQL Server offers you a nice function called NULLIF() to do this:

NULLIF(expression, value) 
-- Returns NULL if "expression" equals to value"
-- Returns "expression", otherwise

NULLIF() can be viewed as the reverse function of ISNULL(). The tutorial script below shows you a good example of using NULLIF():

USE FyiCenterData;
GO

SELECT id, counts FROM fyi_links;
GO
id          counts
----------- -----------
101         NULL
102         8
1101        NULL
202         NULL
2101        NULL
2102        NULL
301         NULL
302         NULL

-- converting NULL to 0
UPDATE fyi_links SET counts=ISNULL(counts,0);
GO
SELECT id, counts FROM fyi_links;
GO
id          counts
----------- -----------
101         0
102         8
1101        0
202         0
2101        0
2102        0
301         0
302         0

-- converting 0 to NULL
UPDATE fyi_links SET counts=NULLIF(counts,0);
GO
SELECT id, counts FROM fyi_links;
GO
id          counts
----------- -----------
101         NULL
102         8
1101        NULL
202         NULL
2101        NULL
2102        NULL
301         NULL
302         NULL

1. What Are NULL Values?
2. How To Assign NULL Values to Variables or Columns?
3. What Happens If NULL Values Are Involved in Arithmetic Operations?
4. What Happens If NULL Values Are Involved in String Operations?
5. What Happens If NULL Values Are Involved in Datetime Operations?
6. What Happens If NULL Values Are Involved in Bitwise Operations?
7. What Happens If NULL Values Are Involved in Comparison Operations?
8. What Happens If NULL Values Are Involved in Boolean Operations?
9. How To Test NULL Values Properly?
10. How To Replace NULL Values in Expressions using ISNULL()?
11. How To Replace Given Values with NULL using NULLIF()?