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 |