Hi All,
I'll try and make this simple.
I have a column in a table that has one or more values separated by a comma.
Ex: 1234,456,322,33445,abce,ekksks
I want to go through each record and take this column data and parse it out,
then insert each element into another table.
ex:
INSERT Into GTable (RecordID, ItemValue)
Values (NewID(), <Parsed Values from each record's
Column> )
Any ideas would be greatly appreciated.
John.Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"John Rugo" wrote:
> Hi All,
> I'll try and make this simple.
> I have a column in a table that has one or more values separated by a comm
a.
> Ex: 1234,456,322,33445,abce,ekksks
> I want to go through each record and take this column data and parse it ou
t,
> then insert each element into another table.
> ex:
> INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
> Column> )
> Any ideas would be greatly appreciated.
> John.
>
>|||Do you mean inserting each value as a separate row? If I were to create the
insert statements manually for your example, would it look like:
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '1234')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '456')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '322')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '33445')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), 'abce')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), 'ekksks')
?
If so, then a cursor would probably be the best way to go (hopefully this is
a one time thing, because as we all know, cursors as the devil)...
If you want some quickly hacked code as an example of how the cursor would
work, post back and I'll see if I can put something together..
-Cliff
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:Ot0XwOsPFHA.4028@.tk2msftngp13.phx.gbl...
> Hi All,
> I'll try and make this simple.
> I have a column in a table that has one or more values separated by a
comma.
> Ex: 1234,456,322,33445,abce,ekksks
> I want to go through each record and take this column data and parse it
out,
> then insert each element into another table.
> ex:
> INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
> Column> )
> Any ideas would be greatly appreciated.
> John.
>|||John,
Here is an efficient way to do this - it's buried in one of the articles
Alejandro referred you to.
/*
A table-valued function with one parameter, a delimited list,
that returns the separate distinct items of the list.
Steve Kass, Drew University
Thanks to MVPs Linda Wierzbicki and Umachandar Jayachandran
for help and helpful discussions on this.
*/
--A table of integers is needed
create table Seq (
Nbr int not null
)
insert into Seq
select top 4001 0
from Northwind..[Order Details]
cross join (select 1 as n union all select 2) X
declare @.i int
set @.i = -1
update Seq
set @.i = Nbr = @.i + 1
alter table Seq add constraint pk_Seq primary key (Nbr)
--table Seq created
go
--This makes things more readable. The list is easier
--to process if it begins and ends with a single comma
--As it turns out also, list items cannot
--have leading or trailing spaces (here any leading spaces
--in the first item or trailing spaces in the last are
--eliminated)
create function RegularizedList (@.List varchar(8000))
returns varchar(8000) as begin
return replace(rtrim(','+ltrim(@.List))+',', ',,', ',')
end
go
--This function returns a table containing one column, commaPos,
--of integers, the positions of each comma in @.List, except the last
--This function returns a table containing the items in the list.
--The items are extracted by selecting those substrings of
--the list that begin immediately after a comma and end
--immediately before the next comma, then trimming spaces on
--both sides.
create function ListTable (@.List varchar(8000))
returns table as return
select
ltrim(rtrim(
substring(regL,
commaPos+1,
charindex(',', regL, commaPos+1) - (commaPos+1))))
as Item
from (
select Nbr as commaPos
from Seq, (
select dbo.RegularizedList(@.List) as regL
) R
where substring(regL,Nbr,1) = ','
and Nbr < len(regL)
) L, (
select dbo.RegularizedList(@.List) as regL
) R
go
--examples
declare @.x varchar(4000), @.time datetime
set @.time = getdate()
set @.x = replicate('foo,bar,foo,bar,ab,',30) + 'end'
select distinct Item from ListTable(@.x)
select datediff(ms,@.time,getdate())
set @.x = '10245 10345 98292 '
declare @.s varchar(400)
set @.s = replace(@.x,' ',',')
select * from ListTable(@.s)
--Note, if a list contains a non-comma delimiter, and contains no
--commas within items, this replacement allows the function to
--handle it. If a comma appears in an item, but some other non-
--delimiter is absent from the list, a three-step replacement can
--be made:
-- replace all commas with new character not in list
-- replace all delimiters with comma
-- Use (select replace(Item,<new>,<comma> ) from ListTable(@.List)) LT
-- where the list table is used.
go
--Since this is a repro script, delete everything!
--Keep them around if they are helpful, though.
DROP FUNCTION RegularizedList
DROP TABLE Seq
DROP FUNCTION ListTable
-- Steve Kass
-- Drew University
John Rugo wrote:
>Hi All,
>I'll try and make this simple.
>I have a column in a table that has one or more values separated by a comma
.
>Ex: 1234,456,322,33445,abce,ekksks
>I want to go through each record and take this column data and parse it out
,
>then insert each element into another table.
>ex:
>INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
>Column> )
>Any ideas would be greatly appreciated.
>John.
>
>
