How To Replace Given Values with NULL using NULLIF()?
Submitted by: AdministratorSometime 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
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
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
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Create a Testing Table with Test Data in MS SQL Server? |
☺ | How To Create a View on an Existing Table in MS SQL Server? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How To Create a Large Table with Random Data for Index Testing in MS SQL Server? |
☺ | How Fixed Length Strings Are Truncated and Padded? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |