SQL Server COALESCE() Function

SQL Server COALESCE() Function returns the first non-null value from a list of expressions.

The SQL Server COALESCE() function is used 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.

SQL Server COALESCE() function syntax is:

COALESCE ( exv1, exv2…, exvN )

Here,

exv1, exv2…, exvN are expression values.

Properties of the Syntax of SQL Server Coalesce function:

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

Let us look at some examples of the COALESCE() function in SQL server.

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, NULL, NULL, NULL, 'GFG') 
AS RESULT ;

Output :

Example 4

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

Output :

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”.

Query:

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

Output :

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 :

• The COALESCE() function in SQL Server is used to handle NULL values effectively by replacing them with user-defined values during expression evaluation.
• All expressions within the COALESCE() function must have the same data type to ensure proper evaluation and return of values.
• The COALESCE() function is available in SQL Server (all supported versions), Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, and Parallel Data Warehouse.