Congratulations to all monthly winners of February 2014, they have won 2950 cash and 19193 worth gifts !
Be Interview ready ! |
Make friends on DotNetFunda.com and expand your network!
Winners

Announcements
Like us on Facebook
Sponsor
Top Articles Author
Sat, 08-Mar-2014 Authors
All Time Authors

(Statistics delayed by 5 minutes)

What is the use of COALESCE in SQL Server?

Posted by Raja on 12/14/2008 | Category: Sql Server Interview questions | Views: 36631
Answer:

Coalesce returns the first non-null expression among its arguments.

Lets say we have to return a non-null from more than one column, then we can use COALESCE function.

`SELECT COALESCE(hourly_wage, salary, commission) AS 'Total Salary' FROM wages`

In this case,

If hourly_wage is not null and other two columns are null then hourly_wage will be returned.
If hourly_wage, commission are null and salary is not null then salary will be returned.
If commission is non-null and other two columns are null then commission will be returned.

Asked In: Many Interviews | Alert Moderator

Found interesting? Add this to:

More Interview Questions by Raja

Comments or Responses

Posted by: Jayanti on: 12/22/2011 | Points: 10

If all 3 attributes are null then?

Posted by: Manaskumarm on: 9/17/2012 | Points: 10

"If all 3 attributes are null then?"
Then it will return as NULL . We can handle following way.

#1. So we need to handle in our server side code.

#2. SELECT ISNULL((CAST(COALESCE(hourly_wage, salary, commission) AS money)),0) AS 'Total Salary'
FROM wages;
It will return 0.00

Posted by: Me_Himanshu on: 3/6/2013 | Points: 10

coalesce function is used to replace the first not null value from an expression
check the below example-
if we have a table having name,mobileno,landline as columns
name mobileno landline
abc 9892133 null
def null 0223111
ghi null 0112233
jkl 998822 null
mno 998299 04488822
pqr null null
then the result of follwing query
select name,coalesce(mobileno,landline) as contact from tablename
would return the result
name contact
abc 9892133
def 0223111
ghi 0112233
jkl 998822
mno 998299 (first not null value)
pqr

Login to post response.