How to parse a string column with comma delimited

[N]

Hi,
I would like to parse out each value that is seperated
by a comma in a field and use that value to join to another table.
What would be the easiest way to do so without having to
write a function or routine ?

EX.

Table AAA
COL1 COL2
1 11, 124, 156
2 11, 505, 600, 700, ...

Table BBB
COL1 COL2
11 Desc11
124 Desc124
156 Desc 156

[Erland Sommarskog]

N ([e-mail]) writes:

I would like to parse out each value that is seperated
by a comma in a field and use that value to join to another table.
What would be the easiest way to do so without having to
write a function or routine ?

EX.

Table AAA
COL1 COL2
1 11, 124, 156
2 11, 505, 600, 700, ...

I hope that you understand that this is a horrible table design, and
if you have any possibility you should change it.

There is article on my web site,
http://www.sommarskog.se/arrays-in-sql.html, where I present a number
of ways to unpack lists into tables. I mainly discuss this in the context
of the list being one single list, sent down to the client. I very
briefly discuss this in the section "Unpacking a Table Column" and make
some suggesting about using a Numbers table. That is, rather than
using the function duo_text_split_ me, you should take the inner parts
of it, and apply the logic to your table.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[lindawie]

N,

Hi,
I would like to parse out each value that is seperated
by a comma in a field and use that value to join to another table.
What would be the easiest way to do so without having to
write a function or routine ?

EX.

Table AAA
COL1 COL2
1 11, 124, 156
2 11, 505, 600, 700, ...

Table BBB
COL1 COL2
11 Desc11
124 Desc124
156 Desc 156
.
.
.

You can do this with a table of seqential numbers.

-- You will need an auxiliary table of numbers.
-- Any table with enough rows will do to create it.
-- The number of rows must be greater than the length
-- of your longest string.

select top 1000 identity(int, 1, 1) Nbr
into Seq from master..syscolu mns
go
alter table Seq add primary key (Nbr)
go
create Table AAA (
COL1 int, COL2 varchar(200)
)
insert AAA values(1, '11, 124, 156')
insert AAA values(2, '11, 505, 600, 700')

create Table BBB (
COL1 int, COL2 varchar(200)
)
insert BBB values(11, 'Desc11')
insert BBB values(124, 'Desc124')
insert BBB values(156, 'Desc 156')
insert BBB values(600, 'Desc 600')
go

--Parse the string in AAA.COL2
select AAA.COL1,
cast(substring( List, Nbr + 1,
charindex(',', List, Nbr + 1) - (Nbr + 1))
as int) Item
from (select COL1, ',' + replace(COL2, ' ', '')+ ','
from AAA) AAA (Col1, List)
join Seq on substring(List, Nbr, 200) like ',_%'
and Nbr between 1 and len(List)
-- Or if you need to get the description from table BBB,
-- you can do it all in one step, without using a #temp table.

select AAA.COL1, AAA.Item, BBB.COL1, BBB.COL2
from (select AAA.COL1,
cast(substring( List, Nbr + 1,
charindex(',', List, Nbr + 1) - (Nbr + 1))
as int) Item
from (select COL1, ',' + replace(COL2, ' ', '')+ ','
from AAA) AAA (Col1, List)
join Seq on substring(List, Nbr, 200) like ',_%'
and Nbr between 1 and len(List)) AAA
join BBB
on BBB.COL1 = AAA.Item
go
drop table AAA
drop table BBB
drop table Seq

/***
COL1 Item
----------- -----------
1 11
1 124
1 156
2 11
2 505
2 600
2 700

COL1 Item COL1 COL2
----------- ----------- ----------- -------------
1 11 11 Desc11
1 124 124 Desc124
1 156 156 Desc 156
2 11 11 Desc11
2 600 600 Desc 600

***/

-- Linda