Saturday, February 6, 2010

Parsing a delimited string


Most often I've seen posts where the requirement is to parse a delimited string and convert it to table of values. Below is a table valued function which I use to parse a delimited string and convert it to table of values


CREATE FUNCTION ParseValues
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(8000))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

Example usage

select * from dbo.ParseValues('This|~~|is|~~|a|~~|delimited|~~|string|~~|value','|~~|')

Result