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
⇒ Boolean Values and Logical Operations in SQL Server Transact-SQL
⇐ ISNULL() - Replacing NULL Values in Expressions in SQL Server
2017-01-29, 4690🔥, 0💬
Popular Posts:
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...
How To Use SQL*Plus Built-in Timers in Oracle? If you don't have a stopwatch/timer and want to measu...
How To Provide Default Values to Function Parameters in SQL Server Transact-SQL? If you add a parame...
How to change the data type of an existing column with "ALTER TABLE" statements in SQL Server? Somet...