MS SQL Server Concepts and Programming Question:
How To Replace Given Values with NULL using NULLIF()?
Answers:
Answer #1Sometime 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
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
Answer #2SELECT 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>
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>
Previous Question | Next Question |
How To Replace NULL Values in Expressions using ISNULL()? | What Is the Security Model Used in SQL Server 2005? |