Results 1 to 4 of 4

Thread: simple loop

  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Post Unanswered: simple loop

    Hi

    Whats the best way to iterate through a comma delimited string and input each value into a new row in a table?

    For example, the following string:

    15,6,5,2,14,

    Needs to be input into a simple table

    id string value
    1 15
    2 6
    3 5
    4 2
    5 14

    thanks

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Google search: sql server loop, http://www.databasejournal.com/featu...le.php/3100621 was the 6th entry.
    Code:
    	declare @counter int
    	set @counter = 0
    	while @counter < 10
    	begin
    	  set @counter = @counter + 1
    	  print 'The counter is ' + cast(@counter as char)
    	end
    This code was given which will help you write the loop to loop through the comma separtate string. To find the comma in the string, I used Google search: string functions in sql server -2005. Which lead me to this site. Here, I used the first one in the list. Found Left, Right, Substring, and CharIndex as functions to use to work my way down the comma separated string.

    I would put all this into a stored procedure, with an INSERT INTO sql line within the loop to write each word to the table you want to add these words to.
    HTH,

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

  4. #4
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Thanks guys for your posts, much appreciated. I ended up using the following:

    Code:
    DECLARE @CommaDelimitedString varchar(50)
    SET @CommaDelimitedString = '7,15,2,16,12,'
    
    DECLARE @Word varchar(10), @Pos int
    
    SET @Pos = CHARINDEX(',', @CommaDelimitedString, 1)
    
    IF REPLACE(@CommaDelimitedString, ',', '') <> ''
    BEGIN
    	WHILE @Pos > 0
    	BEGIN
    		SET @Word = LTRIM(RTRIM(LEFT(@CommaDelimitedString, @Pos - 1)))
    		IF @Word <> ''
    		BEGIN
    			INSERT INTO @testTable(id) VALUES (CAST(@Word AS int)) 
    		END
    	SET @CommaDelimitedString = RIGHT(@CommaDelimitedString, LEN(@CommaDelimitedString) - @Pos)
    	SET @Pos = CHARINDEX(',', @CommaDelimitedString, 1)
    	END
    END

Posting Permissions

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