COALESCE in sql

on
Returns  the       first           nonnull            expression       among its arguments.


COALESCE() accepts a series of values and a value to use in the event that all items in the list are null; then, it returns the first not-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 whose columns include FirstName, MiddleName and 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 A shows how you can turn it into a function. Now whenever you need this script (regardless of what the columns are actually named) just call the function and pass the three columns. In the examples below, I'm passing literals, but you can substitute column 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 the result set:
Cher  
 James  Bond
 John F. Kennedy
You'll notice 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:

Comments

Post a Comment