Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Using Built-in system functions – COALESCE()

Using Built-in system functions – COALESCE()


COALESCE() can be used to return first non-null value from the specified arguments. A typical scenario to use COALESCE() is when you need to retrieve value from another column when the primary column you need is NULL. for example, you need to retrieve all values from ColumnA, but in cases where ColumnA is NULL, you need values from ColumnB.

I have below table which contains NULL values for some rows:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      Title, FirstName, LastName

FROM        dbo.Contacts

Result Set:

Title    FirstName        LastName

——– ————————————————– ————————————————–

Ms.      Gail        Erickson

Mr.      NULL        Goldberg

Ms.      NULL        Galvin

Ms.      Jill        NULL

Mr.      Hung-Fu     NULL

 

(5 row(s) affected)

Now, if I need retrieve single name, I can achieve that by using COALESCE().

SELECT      Title, COALESCE(FirstName,LastName) AS Name

FROM        dbo.Contacts

Result Set:

Title    Name

——– ————————————————–

Ms.      Gail

Mr.      Goldberg

Ms.      Galvin

Ms.      Jill

Mr.      Hung-Fu

 

(5 row(s) affected)

The same output can be achieved by using a CASE expression as well:

SELECT      Title,

            CASE

                  WHEN FirstName IS NOT NULL THEN FirstName

                  WHEN LastName  IS NOT NULL THEN LastName

            END AS Name

FROM        dbo.Contacts

Result Set:

Title    Name

——– ————————————————–

Ms.      Gail

Mr.      Goldberg

Ms.      Galvin

Ms.      Jill

Mr.      Hung-Fu

 

(5 row(s) affected)

Hope This Helps!

Vishal

  1. pavankumarvarma
    19.12.2012 05:40 | #1

    You are giving very decent examples to understand….
    Thank you very much

  1. No trackbacks yet.