SQL Server COALESCE function

COALESCE : COALESCE function in SQL returns the first non-NULL expression among its arguments.

Syntax for COALESCE is as follows:

COALESCE ("expression 1", "expressions 2", ...)

Solution:

Let's start with the documented use of coalesce. According to SQL Server, COALESCE returns the first non-null expression among its arguments.

For example,

SELECT COALESCE(NULL, NULL, NULL, GETDATE())

will return the current date.  It bypasses the first NULL values and returns the first non-null value.

Examples 1 :

SELECT COALESCE(NULL, NULL, 'kiran', NULL, 'getto');

Output : kiran

SELECT COALESCE(NULL, 'getto', 'kiran');

Output : getto

SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4);

Output :1

Example 2 :

Table Contact_Info

Name	Business_Phone	Cell_Phone	Home_Phone
Jeff	531-2531	622-7813	565-9901
Laura	NULL	772-5588	312-4088
Peter	NULL	NULL	594-7477

Run the below Query using COALESCE  :

SELECT Name, COALESCE (Business_Phone, Cell_Phone, Home_Phone) Contact_Phone

FROM Contact_Info;

Result:

Name    Contact_Phone

Jeff        531-2531

Laura     772-5588

Peter     594-7477