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?