How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL?

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

 

⇒Working with NULL Values in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials