Open In App

Use of COALESCE() function in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

Introduction :
The SQL Server COALESCE() function is useful to handle NULL values. The NULL values are replaced with the user-given value during the expression value evaluation process. The SQL Server Coalesce function evaluates the expression in a definite order and always results first not null value from the defined expression list.

Syntax :

COALESCE ( exv1, exv2..., exvN )

Where –
exv1, exv2…, exvN are expression values.

Properties of the Syntax of SQL Server Coalesce function :

  • All expressions must be have same data-type.
  • It could have multiple expressions.

Example-1 :

SELECT COALESCE (NULL, 'X', 'Y') 
AS RESULT ;

Output :

RESULT
X

Example-2 :

SELECT COALESCE (NULL, 13, 24, 35, 46) 
AS RESULT ;

Output :

RESULT
13

Example-3 :

SELECT COALESCE (NULL, NULL, 45, NULL, NULL) 
AS RESULT ;

Output :

RESULT
45

Example-4 :

SELECT COALESCE (NULL, NULL, NULL, NULL, NULL, 'GFG') 
AS RESULT ;

Output :

RESULT
GFG

Example-5 :

SELECT COALESCE (NULL, NULL, NULL, NULL, 5, ‘GFG’) AS RESULT ;

RESULT
5

Output :
when the queries are run in SQL Server Management Studio.

Example-6 :

SELECT COALESCE 
(NULL, NULL, NULL, NULL, NULL, 'GFG', 1)

Output :

Using SQL Server Coalesce function in a string concatenation operation :
Let us suppose we have below table name “GeekName”.

Example-7 :
Output :

Select * from GeekName;
F_Name M_Name L_Name
Manoj M. Kumar
Khushi NULL Modi
Payal K. Chauan
Nisha NULL Gupta
Mina NULL Singh
Kishan C. Maan

Example-8 :

SELECT F_Name + ' ' +M_Name+ ' ' 
+ L_Name FullName FROM GeekName ;

Output :

FullName
Manoj M. Kumar 
NULL
Payal K. Chauan 
NULL
NULL
Kishan C. Maan 

Using SQL server function called COALESCE to handle the NULL values :
The SQL statement will concatenate all three names, but no NULL values will appear in the output.

Example-9 :

SELECT F_Name +' '+COALESCE(M_Name, '') +' '
+ L_Name   FullName  FROM GeekName ;

Output :

FullName
Manoj M. Kumar
Khushi Modi
Payal  K. Chauan  
Nisha Gupta
Mina Singh
Kishan C. Maan  

Last Updated : 10 Dec, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads