SQL Server Performance

parse field to create 1toMany...

Discussion in 'Contribute Your SQL Server Scripts' started by kingofSQL, Jun 17, 2005.

  1. kingofSQL New Member

    /*
    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

Share This Page