Search
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Wed, 22-Oct-2014 Authors
All Time Authors
Vishal@Neeraj
38680
Rajkatie
20600
Bharathi Cherukuri
18040

Latest members | More ...


(Statistics delayed by 5 minutes)
 Interview Question

What is the use of COALESCE in SQL Server?

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

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:



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