January 24, 2006

How to skin a Turkey

Here is a very useful function created to parse a delimited list in TSQL. It is fairly straight forward. Pass the string to be parsed and the delimiter (defaults to a comma) and it returns a table with a single column containing the strings that were delimited together. Why Microsoft doesn't have something like this built in is beyond me.

CREATE FUNCTION dbo.fn_ParseDelimitedList
(
@DelimitedList varchar(8000),
@Delimiter char(1) = ","
)
RETURNS @TableVar TABLE (Item varchar(100) NOT NULL ) 
AS
BEGIN
DECLARE @IDListPosition int 
DECLARE @IDList varchar(8000)
DECLARE @ArrValue varchar(8000) 
DECLARE @Pattern char(3)
SET @IDList = COALESCE(@DelimitedList, '') 
IF @IDList <> '' 
BEGIN 
-- Add Trailing delimiter to end of list so user doesn't have to 
IF RIGHT(@DelimitedList,1) <> @Delimiter
begin
SET @IDList = @IDList + @Delimiter 
END

SET @Pattern = '%' + @Delimiter + '%'
-- Loop through the comma demlimted string list 
WHILE PATINDEX(@Pattern , @IDList ) <> 0 
BEGIN 
SELECT @IDListPosition = PATINDEX(@Pattern , @IDList) 
SELECT @ArrValue = LEFT(@IDList, @IDListPosition - 1) 
-- Insert parsed ID into TableVar for "where in select"
INSERT INTO @TableVar (Item) VALUES (@ArrValue) 
-- Remove processed string 
SELECT @IDList = STUFF(@IDList, 1, @IDListPosition, '') 
END 
END
RETURN 
END