Use of COALESCE() function in SQL Server
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 :
Example-2 :
SELECT COALESCE (NULL, 13, 24, 35, 46)
AS RESULT ;
Output :
Example-3 :
SELECT COALESCE (NULL, NULL, 45, NULL, NULL)
AS RESULT ;
Output :
Example-4 :
SELECT COALESCE (NULL, NULL, NULL, NULL, NULL, 'GFG')
AS RESULT ;
Output :
Example-5 :
SELECT COALESCE (NULL, NULL, NULL, NULL, 5, ‘GFG’) AS RESULT ;
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
Share your thoughts in the comments
Please Login to comment...