Test your technical skills online, appear in Virtual Interview and be ready for job!
Twitter Twitter LinkedIn YouTube Google
Be Interview ready ! | Search
Make friends on DotNetFunda.com and expand your network!

Win Prizes

Like us on Facebook
Top Articles Author
Sat, 19-Apr-2014 Authors
All Time Authors
Bharathi Cherukuri

Latest members | More ...

(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: 38651

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:

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)

Login to post response.

Comments / Responses
Select text & click toolbar to format. Formatting appears in viewmode only. HTML Tags are not allowed.
Bold Italic Underline Paragraph Title Code  Link 
 Wait ... Processing ..... please wait.