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;