|
Execute the following
SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the use of the COALESCE function.
-- SQL coalesce - ANSI-92 SQL standard - SQL isnull - special in T-SQL
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address,
City,
Region=COALESCE(Region,''),
PostalCode, Country, Phone,
Fax=COALESCE(Fax, 'NO FAX')
FROM Northwind.dbo.Customers
ORDER BY CompanyName
GO
/* Partial results
City Region PostalCode Country Phone Fax
Kobenhavn 1734 Denmark 31 12 34 56 31 13 35 57
Paris 75016 France (1) 47.55.60.10 (1) 47.55.60.20
Lander WY 82520 USA (307) 555-4680 (307) 555-6525
Charleroi B-6000 Belgium (071) 23 67 22 20 (071) 23 67 22 21
Portland OR 97201 USA (503) 555-3612 NO FAX
*/
------------
------------
-- SQL coalesce for comma-limited list building
------------
DECLARE @EmpList VARCHAR(128);
SELECT TOP 7 @EmpList =
coalesce(@EmpList + ', ','') + cast(EmployeeID AS VARCHAR(4))
FROM AdventureWorks.HumanResources.Employee
ORDER BY EmployeeID
SELECT EmployeeIDCSV = @EmpList
/* Result
EmployeeIDCSV
1, 2, 3, 4, 5, 6, 7
*/
------------
------------
-- Using coalesce in view definition
------------
-- SQL coalesce
-- SQL view\
-- SQL inner join
USE AdventureWorks
IF object_id(N'Person.vEmployeeName', 'V') IS NOT NULL
DROP VIEW Person.vEmployeeName
GO
CREATE VIEW Person.vEmployeeName
AS
SELECT FirstName,
MiddleName,
LastName,
Name=LastName + ', ' + FirstName + COALESCE(' ' + MiddleName, '')
FROM Person.Contact c
INNER JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
GO
SELECT * FROM Person.vEmployeeName
ORDER BY LastName, FirstName
GO
/* Partial results
FirstName MiddleName LastName Name
Syed E Abbas Abbas, Syed E
Kim B Abercrombie Abercrombie, Kim B
Hazem E Abolrous Abolrous, Hazem E
Pilar G Ackerman Ackerman, Pilar G
Jay G Adams Adams, Jay G
François P Ajenstat Ajenstat, François P
*/
------------
|
