Split - Comma seperaed values

WebDev2000

Hi Guys,

I have a table with 2 fields (ID and DepartmentNames), department names column contains a comma seperated list. Can anyone please help me with a query to return the department names in individual rows

Current format

--------------------

ID DepartmentNames

1 IT,Finance

2 Accounting, Finance, IT

Desired format

----------------------

ID Departmnt Name

1 IT

1 Finance

2 Accounting

2 Finance

2 IT

Kent Waldrop Jn07

One method is to use Jens Suessmeyer's split function; there is an example and the definition of that function here:

http://forums.microsoft.com/TechNet/ShowPost.aspx PostID=419984&SiteID=17

In addition to this, you might want to give a look to this article by MVP Erland Sommarskog:

http://www.sommarskog.se/arrays-in-sql.html

Also, if you have no more than 4 entries in a list and your data is otherwise simple, you might be able to take advantage of the PARSENAME function. Hang on, and I'll show you an example of the PARSENAME work-around.

Kent Waldrop Jn07

You can use PARSENAME with an in-line table provided:

You will never have more than 4 entries in your comma delimited list
Your data will not contain any PERIOD characters

You can still use PARSENAME if you have periods, but you will have to translate them into something else and then translate them back. Use of PARSENAME has the advantage that you are leveraging off a built-in function so it will be fast. The base example is something like this:

Code Snippet

/*
ID DepartmentNames
1 IT,Finance
2 Accounting, Finance, IT
*/

declare @xample table
( ID integer,
DepartmentNames varchar(80)
)
insert into @xample
select 1, 'IT, Finance' union all
select 2, 'Accounting, Finance, IT'
--select * from @xample

select a.ID,
b.theName
from @xample a
cross apply
( select parseName(replace(DepartmentNames,', ','.'), 5 - seg)
as theName
from ( select 1 as seg union all select 2 union all
select 3 union all select 4
) p
where parseName(replace(DepartmentNames,', ','.'), 5 - seg)
is not null
) b

/*
ID theName
----------- -----------
1 IT
1 Finance
2 Accounting
2 Finance
2 IT
*/

The Split code looks like this:

Code Snippet

select a.ID,
b.splitValue
from @xample a
cross apply
( select cast(ltrim(splitValue) as varchar(20))

as splitValue

from dbo.split(departmentNames, ',')

) b

hunchback

Try:

Code Snippet

create table dbo.t1 (

ID int not null unique,

DepartmentNames varchar(128)

)

go

insert into dbo.t1 values(1, 'IT,Finance')

insert into dbo.t1 values(2, 'Accounting, Finance, IT')

go

-- Idea from Itzik Ben-Gan to generate a table of numbers on the fly

WITH

L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),

L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),

L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),

Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)

-- Idea from SQL Server MVP Anith Sen (using table of numbers to split the string)

SELECT

t.[ID],

row_number() over(partition by [ID] order by n.n) as position_in_list,

ltrim(rtrim(substring(t.DepartmentNames, n.n, charindex(N',', t.DepartmentNames + N',', n.n) - n.n))) AS [Value]

FROM

Nums as n

inner join

dbo.t1 as t

on n.n <= convert(int, len(t.DepartmentNames))

AND substring(N',' + t.DepartmentNames, n, 1) = N','

order by

t.[ID]

go

drop table dbo.t1

go

Arrays and Lists in SQL Server 2005

http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum

AMB

WebDev2000

Kent, Thanks for the response. Split function helps, but not sure how to put it all togethed to get the results in the row format which I need.

hunchback

Hi,

You do not need to use a split function in this case. You can split the value of the column directly, joining it to a table of numbers. See example posted.

AMB

Kent Waldrop Jn07

AMB:

Does that solution also scale better

hunchback

Hi Kent,

Scale better if we add more rows, increment the length on the column storing the comma delimited list and more users accessing the table

I do not know, but in this simple test, it yields better performance. May be if we use the CLR function mentioned in Erland's article, then performance could be better for the "cross apply" approach.

Anyway, I wonder why they are storing a comma delimited string in a column. It is not compliant with the first normal form.

Code Snippet

create function dbo.Nums (

@n int

)

returns table

as

return (

WITH

L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),

L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),

L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),

L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),

L5 AS(SELECT 1 AS c FROM L3 AS A, L4 AS B),

Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)

select n

from Nums

where n <= @n

)

go

CREATE FUNCTION dbo.Split

(

@String VARCHAR(4000),

@Delimiter VARCHAR(5)

)

RETURNS @SplittedValues TABLE

(

OccurenceId SMALLINT IDENTITY(1,1),

SplitValue VARCHAR(4000)

)

AS

BEGIN

DECLARE @SplitLength INT

WHILE LEN(@String) > 0

BEGIN

SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN 0 THEN

LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1 END)

INSERT INTO @SplittedValues

SELECT SUBSTRING(@String,1,@SplitLength)

SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0 THEN ''

ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END)

END

RETURN

END

go

create table dbo.t1 (

ID int not null identity unique clustered,

DepartmentNames varchar(4000)

)

go

set nocount on

insert into dbo.t1(DepartmentNames)

select

stuff(

(

SELECT ',' + ltrim(b.n)

FROM dbo.Nums(a.n % 1000) as b

ORDER BY b.n

FOR XML PATH('')

), 1, 1, '')

from

dbo.Nums(2000) as a

set nocount off

go

dbcc freeproccache

dbcc dropcleanbuffers

go

SELECT

t.[ID],

ltrim(rtrim(substring(t.DepartmentNames, n.n, charindex(',', t.DepartmentNames + ',', n.n) - n.n))) AS [Value]

FROM

dbo.t1 as t

inner join

dbo.Nums(4050) as n

on n.n <= len(t.DepartmentNames)

AND substring(',' + t.DepartmentNames, n, 1) = ','

order by

t.[ID]

go

dbcc freeproccache

dbcc dropcleanbuffers

go

select a.ID,

b.splitValue

from dbo.t1 a

cross apply

( select splitValue as splitValue

from dbo.split(departmentNames, ',')

) b

go

drop function dbo.Nums, dbo.Split

go

drop table dbo.t1

go

Join approach - 38 sec

Cross Apply - 49 sec

AMB

Kent Waldrop Jn07

OK. Yeah, I figured that the answer was dependent on the test that was run. When I ran with 32767 rows of fully cached data with 5 logical columns to the comma separated string I got about 2900 ms for Jens' split funtion and about 7700 for the join method. I knew that I hadn't tested enough ways to amount to a real test suite.

Unfortunately, the comma separated list question comes up frequently. I went ahead and marked your response as the answer and clicked helpful.

Kent

hunchback

Hi Kent,

> I went ahead and marked your response as the answer and clicked helpful.

What a coincidence, I did the same with your.

AMB

Split - Comma seperaed values

Re: Split - Comma seperaed values

Re: Split - Comma seperaed values

Re: Split - Comma seperaed values

Re: Split - Comma seperaed values

Re: Split - Comma seperaed values

Re: Split - Comma seperaed values

Re: Split - Comma seperaed values

Re: Split - Comma seperaed values

Re: Split - Comma seperaed values