In this article, I am going to explain you Coalesce Function in SQL-Server with example. Coalesce function returns first NON Null expression in given expression. It will return null when all expressions evaluate to null. Coalesce function takes 'n' number of arguments and arguments can be of any data type.

Syntax

Below is the syntax for Coalesce function.

coalesce( expression1, expression2, ..., expression_n)

Example 1

Let's understand coalesce function with examples. Below is coalesce function with select query.

select coalesce(null, 'Hello')  
select coalesce('Hello', null, null)  
select coalesce(null, null, 'Hello')  
select coalesce('ASPArticles','Hello') 

OUTPUT:HelloHelloHelloASPArticlesHere we can see in above all four select statement, we get first non null expressions.

At least one of the arguments to COALESCE must be a typed NULL

You will get above error, if you pass null as value in all the input arguments. Null are not typed, that is why we get this error.

select coalesce(null,null)

OUTPUT:Msg 4127, Level 16, State 1, Line 1At least one of the arguments to COALESCE must be a typed NULL.

Let's try with null value as typed.

declare @Name varchar(10)
select coalesce(null, @Name)

declare @Name2 varchar(10)=null
select coalesce(null, @Name2)

OUTPUT:NULLNULLHere, we don't get any error because one of argument (second argument) is typed.

Conversion failed when converting the varchar value to data type int

We will get above output when we supply two different data types.

select coalesce(null, 'Hello',1)

OUTPUT:Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Hello' to data type int..

Example 2

Consider the below temporary table Employee where we have 4 columns. Some of columns contains null values and we want to find out first non null values in given FirstName, MiddleName and LastName.

create table #Employee
(
Id int,
FirstName varchar(10),
MiddleName varchar(10),
LastName varchar(10),
)
insert into #Employee values(1,'Rahul',null,null)
insert into #Employee values(2,null,'Ricky',null)
insert into #Employee values(3,null,null,'Sehwag')
insert into #Employee values(4,'Virat','Kohli',null)
insert into #Employee values(5,'Sachin','Ramesh','Tendulkar')

select * from #Employee

1 Rahul NULL NULL
2 NULL Ricky NULL
3 NULL NULL Sehwag
4 Virat Kohli NULL
5 Sachin Ramesh Tendulkar

Now, we will use coalesce function to find out first non null expression in FirstName, MiddleName and LastName in Employee table.

select id, coalesce(FirstName,MiddleName,LastName) as 'Name' from #Employee

OUTPUT:
1 Rahul
2 Ricky
3 Sehwag
4 Virat
5 Sachin

Now, insert one more record with null values in all the name columns.

insert into #Employee values(6,null,null,null)
select * from #Employee
OUTPUT:
1 Rahul NULL NULL
2 NULL Ricky NULL
3 NULL NULL Sehwag
4 Virat Kohli NULL
5 Sachin Ramesh Tendulkar
6 NULL NULL NULL

Here 6th record contains null value in all the columns. Now will use coalesce function.

select id, coalesce(FirstName,MiddleName,LastName) as 'Name' from #Employee
OUTPUT:
1 Rahul
2 Ricky
3 Sehwag
4 Virat
5 Sachin
6 NULL

Here in above output we get null in 6th row because all column contains null for the 6th record. We can use 4th input parameter as 'No Name Provided' in coalesce function as shown below.

select id, coalesce(FirstName,MiddleName,LastName,'No Name Provided') 
as 'Name' from #Employee

OUTPUT:
1 Rahul
2 Ricky
3 Sehwag
4 Virat
5 Sachin
6 No Name Provided

Use Coalesce function in place of Case Condition

Here, we can use coalesce function instead of Case condition as shown below.

select id, case when FirstName is not null then FirstName
when MiddleName is not null then MiddleName
when LastName is not null then LastName
else 'No Name Provided'
end as 'Name'
          ,coalesce(FirstName,MiddleName,LastName,'No Name Provided') 
as 'Name'
from #Employee

OUTPUT:
id Name   Name
1 Rahul   Rahul
2 Ricky   Ricky
3 Sehwag   Sehwag
4 Virat   Virat
5 Sachin   Sachin
6 No Name Provided No Name Provided