Parsing a multi value field to break it into pieces 2

[pms18066]

I have a table with a column witha 30 char array. This field contains UP TO 15 2 char codes stored in it. I also have a SSN and a certnum field for keys. I need to break up this one to many relationship into another table. Has anyone pased a filed and then done an insert into another table for each value in that field. I dont think I can do a for loop??? I can take the length of the field and divide by two to determine how many codes are in the field ie the number of inserts I need. I was just hoping someone had a snippet of code which does something like this. I could do it in vb pretty easy and that is an option although we would like to automate this process in a DTS Job, and have it all in TSQL/SProc...

Thanks

 

[SQLDenis]

do a search for the split function (don't have the thread number at hand)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[tran008]

From the web....

CREATE FUNCTION SPLIT (
@str_in		VARCHAR(8000),
@separator	VARCHAR(4) )
RETURNS @strtable TABLE (strval		VARCHAR(8000))
AS
BEGIN

DECLARE
	@Occurrences	INT,
	@Counter	INT,
	@tmpStr	VARCHAR(8000)

	SET @Counter = 0
        IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator 
              SET @str_in = @str_in + @separator

	SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) -  DATALENGTH(@str_in))/ DATALENGTH(@separator)
	SET @tmpStr	= @str_in

	WHILE @Counter <= @Occurrences 
	BEGIN
		SET @Counter = @Counter + 1
		INSERT INTO @strtable
		VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

		SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)
		
		IF DATALENGTH(@tmpStr) = 0
			BREAK
		
	END
	RETURN 
END