looping through field in table

gleech · #1 (**permalink**) 02-27-03, 07:28

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

Gill

Paul Young · #2 (**permalink**) 02-27-03, 10:10

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

Code:

-- =============================================
-- Create inline function
-- =============================================
IF objectproperty(object_id(N'GetStringElement'),'IsScalarFunction') = 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'),'IsScalarFunction') = 1
	DROP FUNCTION GetStringElement

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

Paul Young · #3 (**permalink**) 02-27-03, 10:29

if functions are not your thing you could try:

Code:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech