Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    20

    Red face Unanswered: 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

    Gill

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •