Comma delimited strings

[tshad]

There are couple of ways you can created comma delimited strings from a select statement. But am confused as to why the string has to be initialized first in one and not in the other.

Using a Coalesce statement you do not have to initialize the string and the string comes out perfect. If you, inititialize the string to '', you will get a leading comma.

If you don't use a Coalesce statement, and don't initialize it, you will get nothing. If you do initialize it, you will get a leading comma.

CREATE TABLE Employees
(
Emp_UniqueID smallint PRIMARY KEY,
Emp_FName varchar(30) NOT NULL,
Emp_LName varchar(30) NOT NULL,
)

INSERT Employees VALUES (1,'Jeff','Bagwell')
INSERT Employees VALUES (2,'Jose','Lima')
INSERT Employees VALUES (3,'Chris','Truby')
INSERT Employees VALUES (4,'Craig','Biggio')

go


DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM Employees

SELECT @EmployeeList
GO

-- IF you initialize the @EmployeeList you will get a leading comma

DECLARE @EmployeeList2 varchar(100)
SELECT @EmployeeList2 = ''


SELECT @EmployeeList2 = COALESCE(@EmployeeList2 + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM Employees

SELECT @EmployeeList2
GO

-- You can do the same thing without Coalesce and must initialize the varchar
-- If you don't, you will get nothing.

DECLARE @EmployeeList3 varchar(100)
SELECT @EmployeeList3 = ''


SELECT @EmployeeList3 = @EmployeeList3 + ', ' +  CAST(Emp_UniqueID AS varchar(5))
FROM Employees

SELECT @EmployeeList3
GO

Why is that?

Thanks,

Tom

 

[gmmastros]

When you initialize a variable, the value is NULL

DECLARE @EmployeeList varchar(100)
Select @EmployeeList

When you concatenate strings, if one of them is NULL, the result will be NULL.

DECLARE @EmployeeList varchar(100)
-- @EmployeeList is NULL
Select @EmployeeList + 'Stuff here'

But am confused as to why the string has to be initialized first in one and not in the other.

It helps to think about this as a loop. It's not really a loop, but thinking of it this way helps. In the Coalesce version, the first time through the loop, @EmployeeList will be NULL. The first parameter is @EmployeeList + ','. Since @EmployeeList is NULL, @EmployeeList + ',' will also be NULL, so COALESCE will return the 2nd parameter, which is an empty string.

COALESCE(@EmployeeList + ', ', '')

Using a Coalesce statement you do not have to initialize the string and the string comes out perfect. If you, inititialize the string to '', you will get a leading comma.

That is what I would expect. If you initialize @EmployeeList to an empty string, then the first parameter would be '' + ', ' which evaluates to a comma.

If you don't use a Coalesce statement, and don't initialize it, you will get nothing.

Because NULL Concatenated with anything results in NULL.

If you do initialize it, you will get a leading comma.

That's because you are putting your comma in front of your data.

SELECT @EmployeeList3 = @EmployeeList3 +  CAST(Emp_UniqueID AS varchar(5)) [!]+ ', ' [/!]
FROM Employees

If you put the comma at the end, you will get a trailing comma instead.


By the way, all of the methods here have a bit of a flaw. You see, if you have NULLS in your data, none of these methods will work, so whenever I need to do this, I always include a where clause on the query, like this:

SELECT @EmployeeList3 = @EmployeeList3 +  CAST(Emp_UniqueID AS varchar(5)) [!]+ ', ' [/!]
FROM Employees
[!]WHERE Emp_UniqueID Is Not NULL[/!]

If you are using SQL2005 or greater, there is a better way to concatenate a comma delimited list using XML.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom