Posts

Showing posts from January, 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…