Jozza.net

Loop over delimited values

Experimental code. Could be used for breaking up query strings, perhaps

DECLARE 
    @DelimitedValues varchar(512) = 'label=some value blah blah|label2=blah blah blah|64=325|label with spaces=1000',
    @Delimiter1 char(1) = '|',
    @Delimiter2 char(1) = '=',
    @StringIndex int = 0,
    @SegmentIndex int = 0,
    @LastStringIndex int = 0,
    @StringSegment varchar(64),
    @Label varchar(64),
    @Value varchar(64);

-- get first delimiter
SET @StringIndex = CHARINDEX(@Delimiter1, @DelimitedValues, @StringIndex)
IF (@StringIndex = 0) BEGIN
    -- no delimiter, set to end of line
    SET @StringIndex = LEN(@DelimitedValues)
END

DECLARE @tempValues TABLE (
    Label varchar(64),
    Value varchar(64)
)

WHILE(@StringIndex > 0) BEGIN
    SET @StringSegment = SUBSTRING(@DelimitedValues, @LastStringIndex, @StringIndex-@LastStringIndex);
    SET @SegmentIndex = CHARINDEX(@Delimiter2, @StringSegment)
    IF (@SegmentIndex > 0) BEGIN
        SET @Label = SUBSTRING(@StringSegment, 0, @SegmentIndex)
        SET @Value = SUBSTRING(@StringSegment, @SegmentIndex+1, LEN(@StringSegment))
        
        INSERT INTO @tempValues (
            Label,
            Value
        ) VALUES (
            @Label,
            @Value
        );    
    END
    SET @LastStringIndex = @StringIndex+1;
    -- get next instance or end of string
    IF (@StringIndex < LEN(@DelimitedValues)) BEGIN
        -- search for string
        SET @StringIndex = CHARINDEX(@Delimiter1, @DelimitedValues, @StringIndex+1)
        IF (@StringIndex = 0) BEGIN
            -- no more delimiters, set to end of line
            SET @StringIndex = LEN(@DelimitedValues)
        END
    END ELSE BEGIN
        -- reached end, reset to zero
        SET @StringIndex = 0
    END
END

SELECT * FROM @tempValues;