|
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? |
