COALESCE() accepts a series of values and a value to use inthe event that all items in the list are null; then, it returns the firstnot-null value. This tip describes two creative uses of the COALESCE()function in SQL Server.
Here is a simple example: You have a table of persons whosecolumns include FirstName, MiddleNameand LastName. The table contains these values:
- John A. MacDonald
- Franklin D. Roosevelt
- Madonna
- Cher
- Mary Weilage
If you want to print their complete names as single strings,here's how to do it with COALESCE():
SELECT FirstName + ' ' +COALESCE(MiddleName,'')+ ' ' +COALESCE(LastName,'')
If you don't want to write that for every query, Listing Ashows how you can turn it into a function. Now whenever you need this script (regardless of what the columns are actuallynamed) just call the function and pass the threecolumns. In the examples below, I'm passing literals, but you can substitutecolumn names and achieve the same results:
SELECT dbo.WholeName('James',NULL,'Bond')
UNION
SELECT dbo.WholeName('Cher',NULL,NULL)
UNION
SELECT dbo.WholeName('John','F.','Kennedy')Here is theresult set:
Cher
James Bond
John F. Kennedy
You'llnotice a hole in our thinking -- there are two spaces in James Bond's name.It's easy to fix this by changing the @result line to the following:
SELECT @Result = LTRIM(@first + ' ' + COALESCE(@middle,'') + ' ') + COALESCE(@last,'')
Here's another use of COALESCE().In this example, I will produce a list of monies paid to employees. The problemis there are different payment arrangements for different employees (e.g., someemployees are paid by the hour, by piece work, with a weekly salary, or bycommission).
Listing Bcontains the code to create a sample table. Here are afew sample rows, one of each type:
1 18.0040 NULL NULL NULL NULL
2 NULL NULL 4.00 400 NULL NULL
3 NULL NULL NULL NULL 800.00 NULL
4 NULL NULL NULL NULL 500.00 600
Use the following code to list the amount paid to employees (regardless of how they are paid)in a single column:
SELECT
EmployeeID,
COALESCE(HourlyWage * HoursPerWeek,0)+
COALESCE(AmountPerPiece * PiecesThisWeek,0)+
COALESCE(WeeklySalary + CommissionThisWeek,0)AS Payment
FROM [Coalesce_Demo].[PayDay]
Here is theresult set:
EmployeeID Payment
1 720.00
2 1600.00
3 800.00
4 1100.00
You mightneed that expression in several places in your application and, although itworks, it isn't very graceful. Thisis how you can create a calculated column to do it:
ALTER TABLE Coalesce_Demo.PayDay
ADD Payment AS
COALESCE(HourlyWage * HoursPerWeek,0)+
COALESCE(AmountPerPiece * PiecesThisWeek,0)+
COALESCE(WeeklySalary + CommissionThisWeek,0)
Now asimple SELECT * displays the pre-calculated results.
Summary
This tip demonstrates some unusual ways and places to applythe power of COALESCE(). In my experience, COALESCE() most often appears within a very specificcontent, such as in a query or view or stored procedure.
You can generalize the use of COALESCE()by placing it in a function. You can also optimize its performance and make itsresults constantly available by placing it in a calculated column.
Posts
