parse field to create 1toMany...

/*
I've seen many cases where end users enter "1 to many" values in a text or memo field.
They'll separate the values by commas or carriage returns.
This code parses that field into separate rows then inserts the results into "MyTable"
You can then create a VIEW (or however way you choose), to join the "child" "MyTable" to your parent table. Note I rem INSERT INTO so you can see the results set*/

DECLARE
@prev_Comma integer,
@next_Comma integer,
@count integer,
@len integer,
@textfield char(50),
@start integer,
@end integer,
@end2 integer,
@lineparsed char(50),
@jobno varchar(10),
@CurPos integer,
@Last_comma integer

DECLARE joblineCursor CURSOR FOR
SELECT 'JOBNO-1234','LINE1,LINE2,LINE3,'



OPEN joblineCursor

-- Perform the first fetch.
FETCH NEXT FROM joblineCursor
INTO @jobno, @textfield


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
set @CurPos = 1 -- current location of the string been looked at.
set @Last_comma = 1 -- last know comma
while @CurPos <= len(@textfield) --keep going until get to end of string.
begin
if substring(@textfield,@CurPos,1) = char(44)
begin
set @lineparsed = ltrim(rtrim(substring(@textfield,@last_comma,(@curPos)-@last_comma)))
select @jobno Jobno, @textfield textfield, @lineparsed lineparsed,@last_comma Last_Comma, @curPos CurPos, len(@textfield) len_textfield
-- INSERT INTO MyTable (jobno,textfield,lineparsed)values(@Jobno,@textfield,@lineparsed)
set @Last_comma = @CurPos
set @Last_comma = @Last_comma + 1
end
else
if @CurPos = Len(@textfield) -- when
begin
set @lineparsed = ltrim(rtrim(substring(@textfield,@last_comma,len(@textfield))))
if @lineparsed <> ''
select @jobno Jobno, @textfield textfield, @lineparsed lineparsed,@last_comma Last_Comma, @curPos CurPos, len(@textfield) len_textfield
-- INSERT INTO MyTable (jobno,textfield,lineparsed)values(@Jobno,@textfield,@lineparsed)
end
set @CurPos = @CurPos +1 -- Go to the next Position in the string
end
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM joblineCursor
INTO
@jobno,
@textfield

END

CLOSE joblineCursor
DEALLOCATE joblineCursor


it's all good