SQL Server COALESCE() Function Example
Let us look at some examples of the COALESCE() function in SQL server.
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, NULL, NULL, NULL, 'GFG')
AS RESULT ;
Output :
RESULT |
---|
GFG |
Example 4
SELECT COALESCE (NULL, NULL, NULL, NULL, 5, 'GFG')
AS RESULT ;
Output :
RESULT |
---|
5 |
COALESCE() Function SQL Server Management Studio Example
Here, we will run the COALESCE() Function in SQL Server Management Studio.
Query:
SELECT COALESCE
(NULL, NULL, NULL, NULL, NULL, 'GFG', 1)
Output :
SQL Server Coalesce function in a string concatenation operation Example
Let us suppose we have below table name “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 |
Query:
SELECT F_Name + ' ' +M_Name+ ' '
+ L_Name FullName FROM GeekName ;
Output :
FullName |
---|
Manoj M. Kumar |
NULL |
Payal K. Chauan |
NULL |
NULL |
Kishan C. Maan |
SQL Server COALESCE function to handle the NULL values Example
The SQL statement will concatenate all three names, but no NULL values will appear in the output.
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 |
SQL Server COALESCE() Function
SQL Server COALESCE() Function returns the first non-null value from a list of expressions.
Contact Us