Collections:
NULLIF() - Replacing Given Values with NULL in SQL Server
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL?
✍: FYIcenter.com
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
Â
2017-01-29, 3272👍, 0💬
Popular Posts:
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...
What Is ISAM in MySQL? ISAM (Indexed Sequential Access Method) was developed by IBM to store and ret...
How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...
Where to find answers to frequently asked questions on SQL Transaction Management in Oracle? Here is...
What Is Oracle in Oracle? Oracle is a company. Oracle is also a database server, which manages data ...