What is use of COALESCE and ISNULL?

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

Check the following link

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Coalesce returns first non-null item from multiple expressions.

ex: - select coalesce(null,null,'a',null) will return 'a'.

Isnull can replaces null with a specified value

ex:- select isnull(null,'a') . Note we can specify only one expression.

Another major difference many a times overlooked is the return type.

Isnull returns the type as that of the checked expression.

Ex:-declare @a varchar(1)

declare @b-2 varchar(2)

set @a = NULL

SET @b-2 = 'BC'

select isnull(@a,@b). This will return only 1 character 'B' bcoz length of @a (check expression) is 1.

Coalesce returns the biggest data type among expressions.

In the above mentioned ex:-

select coalesce (@a,@b) retruns 'BC'. @b-2 has the biggest data type amongst the expressions.

"Keep Trying"

RCRock Right there with Babe Points: 718 More actions · Answer 3

Thanks a lot. Both replies helped me a lot to get clear undersatnding.