What is the use of COALESCE in SQL Server?

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

Post |

Interview FAQs |

Exclusive Questions |

Interview Home

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

Bookmark It

< Previous : What are joins in SQL Server?

Next > : What is OPENXML in SQL Server?

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

More Interview Questions by Raja

Latest Interview Questions