COALESCE in Sql Server

  • Facebook
  • Twitter
  • Delicious
  • StumbleUpon
  • Pinterest
  • Google Plus

Definition of COALESCE

COALESCE returns the first non-null expression among its arguments.

COALESCE is one of the most useful function in sql. I have used it many times especially to concatenate many rows into a single text. We can also use ISNULL for similar task but COALESCE is more efficient.

Difference between ISNULL and COALESCE

  • ISNULL is limited to two arguments but in COALESCE we can use more number of arguments
  • Performancewise COALESCE is faster than ISNULL
  • COALESCE is ANSI SQL standard whereas ISNULL is a proprietary TSQL function
  • Using select ISNULL(NULL, NULL) doesn’t throw error but select COALESCE(NULL, NULL) throw error

COALESCE Sample Examples

SELECT COALESCE(NULL, NULL, NULL, 'Sachin Tendulkar')

Output: Sachin Tendulkar

SELECT COALESCE(NULL, NULL, 'Rahul', 'Sachin Tendulkar')

Output: Rahul

SELECT COALESCE('Gangully', NULL, 'Rahul', 'Sachin Tendulkar')

Output: Gangully

Concatenate many rows into a single text string using COALESCE function

Suppose you are having table row values like

Sachin
Micheal
Ronaldo
Lee
Obama

use this below query

DECLARE @CONSTR VARCHAR(MAX)
SELECT @CONSTR = COALESCE(@CONSTR +',' ,'') + Name
FROM VIPTABLE
SELECT @CONSTR

Output : Sachin,Micheal,Ronaldo,Lee,Obama

How to pronounce COALESCE?

koe-uh-less

Introduction to NULLIF, ISNULL, and COALESCE in SQL Server 2005/2008

Like(0)Dislike(0)

Share and Enjoy

rathnakumaravel@yahoo.com'

Rathna

I m Rathina kumaravel (Ramki) and i m a software Engineer in Chennai. I have achieved Bachelor of engineering in Computer Science. I’m a simple person who hides a thousand feelings behind the happiest smile.

Leave a Reply