Sunday, 20 August 2017

Coalesce function in SQL-Server with example

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:
Hello
Hello
Hello
ASPArticles
Here 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 1
At 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:
NULL
NULL
Here, 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
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON