Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » System FunctionsRSS Feeds

COALESCE Function

Posted By: Sarita Patel     Category: SQL Server     Views: 19079

This article explains about COALESCE function in sql server with examples.

COALESCE function is used to get first nonnull expression among specified arguments. If all arguments are NULL then COALESCE returns NULL.

It is a deterministic i.e It returns same value every time it is called with a specific set of values.



Syntax of COALESCE Function :

COALESCE ( expression [ ,...n ] )

expression is any valid sql server expression.

n is a placeholder indicates that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.

Return type of COALESCE function is same as expression.




Examples of COALESCE Function :

Example 1 : Use of COALESCE function in select clause 

SELECT COALESCE(10/2,NULL)

Output
5

Above example returns nonnull expression result i.e 5




Example 2 : Use of COALESCE function to display column value of table in select clause


SELECT ProductName, COALESCE(UnitPrice * Quantity,NULL) AS Total
FROM    Invoices

Output
ProductName                                      Total
Scottish Longbreads                                225
Queso Cabrales                                       840
Côte de Blaye                                      10540
Alice Mutton                                           585
Sasquatch Ale                                         140
Jack's New England Clam Chowder         135.1
Nord-Ost Matjeshering                            388.35

Above example returns nonnull expression.
  
Share: 

 
 

Didn't find what you were looking for? Find more on COALESCE Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of COALESCE Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!