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, 4621🔥, 0💬
Popular Posts:
What are single-byte character string data types supported in SQL Server Transact-SQL? Single-byte c...
How To Convert Character Strings into Numeric Values in SQL Server Transact-SQL? Sometimes you need ...
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...
How To Connect ASP Pages to Oracle Servers in Oracle? If you are running Windows IIS Web server and ...
How to set the current database in SQL Server? Once you are connected to the SQL Server, you should ...