.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

what is the exact use of coalesce function in sql server?

Posted By: Deco     Posted Date: September 14, 2010    Points:2   Category :Sql Server
please prompt the exact use. i googled and cameback with the mess...

Responses
Author: Syed Shakeer Hussain             
Posted Date: September 14, 2010     Points: 5   

Hi Alwyn,

Check the below link for more information with example.

http://www.dbtalks.com/UploadFile/manish123/110/?ArticleID=e6f92748-a05c-40eb-8a98-3fb1353da265

Author: Deco             
Posted Date: September 14, 2010     Points: 5   

Syed,

It seems like ISNULL and Coalesce function are same.

Can you please clarify my doubt?

Regards,
Deco
Author: Majith             
Posted Date: September 14, 2010     Points: 5   

Hi Alwyn,

Coalesce returns the first nonnull expression among its arguments.

Example :

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

ELSE expressionN


ISNULL and COALESCE both are equivalent but it behaves differently.

An expression involving ISNULL with non-null parameters is considered to be NOT NULL,
while expressions involving COALESCE with non-null parameters is considered to be NULL.

Also check the link
http://msdn.microsoft.com/en-us/library/ms190349.aspx




Author: Rajshree             
Posted Date: September 15, 2010     Points: 5   

COALESCE(arg1,arg2,...) is a pretty useful function in SQL. Suppose we have a table A having 3 columns FullName, CompleteName and DisplayName. Any of these columns can contain null values. Now we want to select the DisplayName from this table, but if it is null, then return FullName, if that is also null then return CompleteName. We can easily perform the same in one select statement as:

SELECT COALESCE(DisplayName, FullName, CompleteName) From A

This will return the first non-null value. And best thing is that COALESCE is an ANSI standard.

Here is a nice comparison of CAOLESCE() and ISNULL() (which is a T SQL function):

http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend