.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Sandeep Singh
Melody Anderson
Eminent IT

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Coalesce function in Sql server

Posted By:Deco       Posted Date: October 25, 2010    Points: 50    Category: DataBase    URL: http://www.dotnetspark.com  

This article explains the use of Coalesce() function. When and where this should be applied.

Coalesce() accepts a list of expressions or columns and returns the first non-null value. The syntax of the coalesce function isĀ  as follows:
Coalesce(expression, expression, ...)
Coalesce() is derived from the Latin words co + alesce, which mean to unite toward a common
end, to grow together, or to bring opposing sides together for a common good. The SQL
keyword, however, is derived from the alternate meaning of the term-"to arise from the
combination of distinct elements." In a sense, the coalesce() function brings together multiple,
differing values of unknown usefulness, and from them emerges a single valid value.

Functionally, coalesce() is the same as the following case expression:

WHEN expression1 IS NOT NULL THEN expression1
WHEN expression2 IS NOT NULL THEN expression2
WHEN expression3 IS NOT NULL THEN expression3

The following code sample demonstrates the coalesce() function returning the first non-null
value. In this case, it's 1+2:

SELECT Coalesce(NULL, 1+NULL, 1+2, 'abc')

Coalesce() is excellent for merging messy data. For example, when a table has partial data
in several columns, the coalesce() function can help pull the data together. In one project I
worked on, the client had collected names and addresses from several databases and applications
into a single table. The contact name and company name made it into the proper
columns, but some addresses were in Address1, some were in Address2, and some were in
Address3. Some rows had the second line of the address in Address2. If the address columns
had an address, then the SalesNote was a real note. In many cases, however, the addresses
were in the SalesNote column. Here's the code to extract the address from such a mess:

SELECT Coalesce(
Address1 + str(13)+str(10) + Adress2,
SalesNote) AS NewAddress
FROM TempSalesContacts

For each row in the TempSalesContacts table, the coalesce() function will search through
the listed columns and return the first non-null value. The first expression returns a value
only if there's a value in both Address1 and Address2, because a value concatenated with a
null produces a null. Therefore, if a two-line address exists, then it will be returned.
Otherwise, a one-line address in Address1, Address2, or Address3 will be returned. Failing
those options, the SalesNote column will be returned. Of course, the result from such a
messy source table will still need to be manually scanned and verified.

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend