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, 4796🔥, 0💬
Popular Posts:
How To Count Rows with the COUNT(*) Function in SQL Server? If you want to count the number of rows,...
How To Convert Character Strings into Numeric Values in SQL Server Transact-SQL? Sometimes you need ...
Is SQL Server Transact-SQL case sensitive? No. Transact-SQL is not case sensitive. Like the standard...
What Is an Oracle Tablespace in Oracle? An Oracle tablespace is a big unit of logical storage in an ...
How To Convert Binary Strings into Integers in SQL Server Transact-SQL? Binary strings and integers ...