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, 2696👍, 0💬
Popular Posts:
What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initializatio...
What is test testing area for? The testing area is provided to allow visitors to post testing commen...
What Is an Oracle Tablespace in Oracle? An Oracle tablespace is a big unit of logical storage in an ...
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...
How To Get a List of All Tables with "sys.tables" View in SQL Server? If you want to see the table y...