SQL COALESCE Function

The SQL COALESCE function is used to return the first not Null value from the series of expressions. In this article we will show you, How to write, or Use Coalesce in SQL Server with example

SQL COALESCE Syntax

Before getting into the practical example, the basic syntax behind the Coalesce in SQL Server is as follows:

SQL COALESCE Function example

In this example we will show you, How to use the SQL Server Coalesce function on String data.

SQL CODE

OUTPUT

ANALYSIS

Below statement will return Apple as the Output because, Coalesce function will return the first not Null value

Below statement will return Orange as the Output because, Coalesce function will return the first not-Null value.

SQL COALESCE Function example 2

This example will show you the working functionality of SQL Server Coalesce function on Numerical values

SQL CODE

OUTPUT

SQL COALESCE Function – Practical example

In this example we will show you, How to write a Coalesce in SQL Server . For this demonstration, We are going to use the [Emp] table present in our [SQL Tutorial] Database.

SQL CODE

Following screenshot will show you the data inside the Emp table, as you can see it has 15 records.

In this example, we will use SQL Server Coalesce function to find out the contact number of each employee:

1. If an employee has an Office Phone number, return the office phone number.
2. If an employee does not have an Office Phone number, but he/she has a Mobile phone then, return the Mobile phone number.
3. If our employee in Emp table does not have an Office Phone number, Mobile phone number but, he/she has a Home phone then, return the Home phone number.
4. If an employee has an Office, Mobile, and Home phone number, then Coalesce SQL will return an office phone number.

SQL CODE

OUTPUT

Thank You for Visiting Our Blog