SQL Server COALESCE

In this post I will explain about
sql server coalesce function with example.

The SQL Server COALESCE expression accepts a two or more arguments, evaluates them in order from left to right, and returns the first non-null argument or value.

The following line illustrates the syntax for COALESCE expression:
COALESCE(val1,[val2,…,valn])

SQL Server COALESCE
SQL Server COALESCE

In the above syntax, val1, val2, … valn are different scalar expressions that evaluate to scalar values. The COALESCE expression will returns the first non-null expression. In case all expressions evaluate to NULL, then the COALESCE expression return NULL;

As COALESCE is an expression, It can be used with any clause that accepts an expression.
Ex. SELECT, WHERE, GROUP BY, and HAVING.

SQL Server COALESCE expression examples

Let’s see examine some real time examples of using the COALESCE expression.

(A) Using COALESCE expression with character string data example

The following example uses the COALESCE expression to return the string ‘php’ because it is the first non-null argument:

SELECT 
    COALESCE(NULL, 'php', 'java', NULL,'python') string_result;

Below is output for the above query:

result
------
php
 
(1 row affected)

 

(B) Using COALESCE expression with the numeric data example

Below example uses the COALESCE expression to evaluate a list of numeric arguments and to return the first number.

SELECT 
    COALESCE(NULL, 30, NULL, 40) number_result;

The output is as follows:

result
-----------
30
 
(1 row affected)

 

Summary

You can also read about ASP.NET, JQuery, JSON, C#.Net

I hope you get an idea about sql server coalesce function.
I would like to have feedback on my blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you liked this post, don’t forget to share this.