SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 what is the use of coalesce in sql?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ammuhere
Starting Member

8 Posts

Posted - 10/04/2013 :  23:01:21  Show Profile  Reply with Quote


I'm new to sql server. I googled the use of coalesce and find out it is another words a replace of ISNULL.

I came across a piece of code posted in the forum about the different uses of coalesce.

use adventureworks

DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')

SELECT @DepartmentName AS DepartmentNames

which return the result in a single line.

So why sql does not support string concatenation by default as in .NET like below?
DECLARE @DepartmentName VARCHAR(1000)

    **SELECT @DepartmentName = @DepartmentName + Name + ';'**
    FROM HumanResources.Department
    WHERE (GroupName = 'Executive General and Administration')

    SELECT @DepartmentName AS DepartmentNames



what is the use of coalesce in the below line
SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';' 


and why
**SELECT @DepartmentName = @DepartmentName + Name + ';'**
    FROM HumanResources.Department
    WHERE (GroupName = 'Executive General and Administration')


is not working?

jethrow
Starting Member

USA
37 Posts

Posted - 10/05/2013 :  03:12:57  Show Profile  Reply with Quote
Note that a zero-length string does not equal NULL (unknown). COALESCE returns the first non-null parameter. Concatenating a zero-length string with a second string will result in the second string. Concatenating NULL with a second string will result in NULL.

On a side note - I'd recommend using COALESCE over ISNULL. SQL Svr ISNULL, imo, is somewhat confusing if you have a VBA background.

Microsoft SQL Server Noobie

Edited by - jethrow on 10/05/2013 03:14:47
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/07/2013 :  05:32:41  Show Profile  Reply with Quote
The main reason is because just when you declare a variable its value defaults to NULL.
So as specified in previous post concatenation with it will result in NULL value.

You can do below though in whih case you can keep code same as in .NET


DECLARE @DepartmentName VARCHAR(1000)
SET @DepartmentName =''
SELECT @DepartmentName = @DepartmentName + Name + ';'
    FROM HumanResources.Department
    WHERE (GroupName = 'Executive General and Administration')

    SELECT @DepartmentName AS DepartmentNames


This is because in above case you're explicitly initialising it to blank ('') before the concatenation step

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 10/07/2013 :  08:43:21  Show Profile  Reply with Quote
quote:
Originally posted by ammuhere



I'm new to sql server. I googled the use of coalesce and find out it is another words a replace of ISNULL.




COALESCE acts slightly different to ISNULL in 2 different ways. ISNULL can only replaces with one value where as COALESCE replaces to the n'th.

ISNULL will always retain the target column not null constraint if being used to SELECT INTO statement to create a new table where as COALESCE can be used to manipulate the new column to be all NULL marks.

Run the below and then look at the different column constraints:

IF OBJECT_ID ('RobNullTest', 'U') IS NOT NULL
DROP TABLE RobNullTest;
GO

CREATE TABLE RobNullTest
(Col1 INT UNIQUE IDENTITY(1,1) NOT NULL, Col2 varchar(20) NOT NULL)

INSERT INTO RobNullTest (Col2)
VALUES ('Value');
GO


IF OBJECT_ID ('RobNullTestISNULL', 'U') IS NOT NULL
DROP TABLE RobNullTestISNULL;
GO

SELECT
Col1
,ISNULL(Col2, NULL) as Col2
INTO
RobNullTestISNULL
FROM
RobNullTest;
GO

IF OBJECT_ID ('RobNullTestCOLL', 'U') IS NOT NULL
DROP TABLE RobNullTestCOLL;
GO

SELECT
Col1
,COALESCE(Col2, NULL) as Col2
INTO
RobNullTestCOLL
FROM
RobNullTest;
GO


/* Run to tidy up

IF OBJECT_ID ('RobNullTest', 'U') IS NOT NULL
DROP TABLE RobNullTest;
GO

IF OBJECT_ID ('RobNullTestISNULL', 'U') IS NOT NULL
DROP TABLE RobNullTestISNULL;
GO

IF OBJECT_ID ('RobNullTestCOLL', 'U') IS NOT NULL
DROP TABLE RobNullTestCOLL;
GO

*/

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 10/07/2013 :  13:48:32  Show Profile  Reply with Quote
I'd also recommend using COALESCE over ISNULL, but they each have slight differences in behavior. So, I'd suggest you do a search for the differences. Here is a link to one such discussion:
http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000