DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use the NULLIF function with COALESCE?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the usage of NULLIF function.

-- NULLIF usage - QUICK SYNTAX - ISNULL function - COALESCE function

-- Get prefix before comma or entire string if there is no comma present

SELECT ProductNumber,

       NamePrefix=LEFT(Name,COALESCE(NULLIF(CHARINDEX(',',Name)-1,-1),LEN(Name))),

       ProductName=Name

FROM AdventureWorks2008.Production.Product

ORDER BY ProductName

/* ProductNumber  NamePrefix              ProductName

FR-R38R-60        LL Road Frame - Red     LL Road Frame - Red, 60

FR-R38R-62        LL Road Frame - Red     LL Road Frame - Red, 62

FW-R623           LL Road Front Wheel     LL Road Front Wheel

HB-R504           LL Road Handlebars      LL Road Handlebars    .....*/

------------

-- T-SQL preventing invalid parameter error in LEFT function when marker not found
DECLARE @string varchar(128) = 'SQL Server 2008'
SELECT LEFT(@string, NULLIF(CHARINDEX('|',@string)-1,-1))
-- NULL
SELECT COALESCE(LEFT(@string, NULLIF(CHARINDEX('|',@string)-1,-1)),'')
-- empty string
SET @string = '123456|ABCDEF'
SELECT LEFT(@string, NULLIF(CHARINDEX('|',@string)-1,-1))
-- 123456
----------
-- SELECT NULLIF quick demo - count all, including ListPrice=0.0
SELECT COUNT(ListPrice)
FROM   AdventureWorks2008.Production.Product
-- 504

-- Counts only when ListPrice != 0 - does not count NULLs (ListPrice = 0.0)

SELECT COUNT(NULLIF(ListPrice,0.0))
FROM   AdventureWorks2008.Production.Product
-- 304

------------

------------

-- Parse domain name from Email

------------

DECLARE @T TABLE (

  Email VARCHAR(128))

 

INSERT INTO @T

SELECT 'billt@yahoo.com'

UNION ALL

SELECT 'newton@hotmail.co.uk'

UNION ALL

SELECT 'elvis.p@live.com'

UNION ALL

SELECT 'NYC'

 

SELECT SUBSTRING(Email, NULLIF(CHARINDEX('@', Email), 0) + 1, LEN(Email) -

              CHARINDEX('@', Email) + 1) AS DomainName

FROM   @T 

/* DomainName

yahoo.com

hotmail.co.uk

live.com

NULL  */

------------


-- SQL NULLIF: returns NULL when the two operands are equivalent, else the first one
-- SQL COALESCE
USE tempdb
GO
 
CREATE TABLE Budget (
  BudgetID     TINYINT    IDENTITY,
  DepartmentID TINYINT,
  CurrentYear  DECIMAL    NULL,
  PriorYear    DECIMAL    NULL);
GO
 
INSERT Budget VALUES(10,2000000,1800000);
INSERT Budget VALUES(20,NULL,3000000);
INSERT Budget VALUES(30,0,8000000);
INSERT Budget VALUES(70,NULL,3500000);
INSERT Budget VALUES(90,6000000,6500000);
GO
SELECT * FROM Budget
GO
/*
BudgetID    DepartmentID      CurrentYear       PriorYear
1           10                2000000           1800000
2           20                NULL              3000000
3           30                0                 8000000
4           70                NULL              3500000
5           90                6000000           6500000
*/
 
SELECT COALESCE(CurrentYear,PriorYear) AS 'Yearly Budget'
FROM   Budget;
/*
Yearly Budget
2000000
3000000
0
3500000
6000000
*/
 
-- SQL NULLIF changes 0 to NULL
SELECT NULLIF(COALESCE(CurrentYear,PriorYear),0.00) AS 'Yearly Budget No Zeros'
FROM   Budget;
/*
Yearly Budget No Zeros
2000000
3000000
NULL
3500000
6000000

*/
 
-- SQL NULLIF
SELECT AVG(NULLIF(COALESCE(CurrentYear,PriorYear),0.00)) AS 'Average Yearly Budget'
FROM   Budget;
GO
 
/* Result
 
Average Yearly Budget
3625000.000000
*/
 
-- Cleanup
DROP TABLE tempdb.dbo.Budget
GO

Related article:

How to split CSV string list inline fast?

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE