Skip to content
Related Articles

Related Articles

Use of COALESCE() function in SQL Server
  • Last Updated : 10 Dec, 2020

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_NameM_NameL_Name
ManojM.Kumar
KhushiNULLModi
PayalK.Chauan
NishaNULLGupta
MinaNULLSingh
KishanC.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  

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :