MS SQL Server Interview Question:

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

Submitted by: Administrator

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 GlobalGuideLineDatabase;
GO

SELECT id, counts FROM ggl_links;
GO
<pre>id counts
----------- -----------
101 NULL
102 8
1101 NULL
202 NULL
2101 NULL
2102 NULL
301 NULL
302 NULL</pre>
-- converting NULL to 0
UPDATE ggl_links SET counts=ISNULL(counts,0);
GO
Submitted by: Administrator

SELECT id, counts FROM ggl_links;
GO
<pre>id counts
----------- -----------
101 0
102 8
1101 0
202 0
2101 0
2102 0
301 0
302 0</pre>
-- converting 0 to NULL
UPDATE ggl_links SET counts=NULLIF(counts,0);
GO
SELECT id, counts FROM ggl_links;
GO
<pre>id counts
----------- -----------
101 NULL
102 8
1101 NULL
202 NULL
2101 NULL
2102 NULL
301 NULL
302 NULL</pre>
Submitted by: Administrator