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..syscolumns
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