looping through field in table

Dear All

I have a table, and in one of the fields is the following information:
1,2,3,4,5, etc...

How do I 'loop' through this field to remove each comma and put the number into it's own field in another table - so remove the first comma, and put the number 1 into its own field, remove the 2nd comma and put the number 2 into it's own field etc, until all the numbers are in their own fields.

So it ends up being like this:

Col1 Col2 Col3 Col4 Col5 Col n.............
1 2 3 4 5 n...........

There may not be 5 numbers in the field, sometimes more, sometimes less, so i need to be able to tell when there are no more commas and numbers left

Your help is much appreciated

Thanks

GillI have a function for extracting the nth element of a delimited string, if you can use functions try this:

-- =============================================
-- Create inline function
-- =============================================
IF objectproperty(object_id(N'GetStringElement'),'IsS calarFunction') = 1
DROP FUNCTION GetStringElement
GO

CREATE FUNCTION GetStringElement(
@.String varchar(100)
, @.Element int
, @.Seperator char(1) = ',')

RETURNS varchar(100)
AS
begin
declare @.Pass int, @.Index int, @.LastIndex int, @.Return varchar(100)

if (@.String like '%' + replicate(',%',@.Element - 1)) begin
select @.Pass = 1
, @.Index = 1
, @.LastIndex = 0

while (@.Pass <= @.Element) begin
select @.LastIndex = case @.Index when 1 then 0 else @.Index end
, @.Index = charindex(',',@.String,@.Index + 1)
, @.Pass = @.Pass + 1
end

if (@.LastIndex > 0 and @.Index = 0) set @.Index = len(@.String) + 1

set @.Return = substring(@.String,@.LastIndex + 1, @.Index - @.LastIndex - 1)
end

RETURN @.Return
end
GO

if object_id('tempdb..#psy') is not null
drop table #psy

create table #psy(f1 int identity(1,1) not null,f2 varchar(25),col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))

insert into #psy (f2) values('1,2,3,4,5')
insert into #psy (f2) values('a,b,c')
insert into #psy (f2) values('George,John,Paul,Ringo')

select * From #psy

declare @.pass int, @.ColCount int, @.TSQL varchar(255)
select @.pass = 1
, @.ColCount = 6
while (@.pass <= @.ColCount) begin
select @.TSQL = 'update #psy ' +
'set col' + cast(@.pass as varchar) + ' = dbo.GetStringElement(f2,' + cast(@.pass as varchar) + ',default)'
, @.pass = @.pass + 1
exec(@.TSQL)
end

select * from #psy

IF objectproperty(object_id(N'GetStringElement'),'IsS calarFunction') = 1
DROP FUNCTION GetStringElement

if object_id('tempdb..#psy') is not null
drop table #psy|||if functions are not your thing you could try:
if object_id('tempdb..#psy') is not null
drop table #psy

create table #psy(f1 int identity(1,1) not null,f2 varchar(25),col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))

insert into #psy (f2) values('1,2,3,4,5')
insert into #psy (f2) values('a,b,c')
insert into #psy (f2) values('George,John,Paul,Ringo')

select * From #psy

declare @.RecordID int, @.TSQL varchar(255), @.pass int
, @.Index int, @.LastIndex int, @.Return varchar(100)
, @.String varchar(100), @.Element int

select @.RecordID = min(f1) from #psy
while (@.RecordID is not null) begin
select @.String = f2 from #psy where f1 = @.RecordID

select @.Index = 1
, @.LastIndex = 0
, @.pass = 1

while (@.Index > 0) begin
select @.LastIndex = case @.Index when 1 then 0 else @.Index end
, @.Index = charindex(',',@.String,@.Index + 1)

if (@.LastIndex > 0 and @.Index = 0)
set @.Return = substring(@.String,@.LastIndex + 1, 100)
else
set @.Return = substring(@.String,@.LastIndex + 1, @.Index - @.LastIndex - 1)

select @.TSQL = 'update #psy ' +
'set col' + cast(@.pass as varchar) + ' = ''' + @.Return + ''' ' +
'where f1 = ' + cast(@.RecordID as varchar)
, @.pass = @.pass + 1

exec(@.TSQL)
end

select @.RecordID = min(f1) from #psy where f1 > @.RecordId

end

select * from #psy