Split A Delimited List Into A Table

• Tweet
• 

/*
* Creates a table out of a delimited list
* Input:
* 	@List nvarchar(4000) - delimited list to be split
*		@Del nvarchar(10) - delimiter (trailing spaces are ignored)
* Output: 2 Column table, with columns ListID int, and ListItem nvarchar(200)
* Usage: 
* 	SELECT * FROM Foo 
* 	WHERE FooBar IN (SELECT ListItem FROM dbo.fnSplit('Foo','Bar','FooBar'))
* Updated 08/30/04 by Oskar Austegard
*/
ALTER FUNCTION dbo.fnSplit
(
	@List nvarchar(4000), 
	@Del nvarchar(10) = ','
)
RETURNS @ListTable TABLE 
(
	ListID int IDENTITY , 
	Item nvarchar(200)
)
AS
BEGIN
	DECLARE @LenDel int
	DECLARE @Pos int
	DECLARE @Item nvarchar(200)
	
	--Get the length of the delimiter, use hack to get around LEN(' ') = 0 issue
	SET @LenDel = LEN(@Del + '|') - 1 

	SET @Pos = CHARINDEX(@Del, @List)
	WHILE @Pos > 0
	BEGIN
		--Get the item
		SET @Item = SUBSTRING(@List, 1, @Pos-1)
		--Add it to the table (if not empty string) 
		IF LEN(LTRIM(@Item)) > 0
			INSERT @ListTable (Item) VALUES (LTRIM(@Item))
		--Remove the item from the list
		SET @List = STUFF(@List, 1, @Pos+@LenDel-1, '')
		--Get the position of the next delimiter
		SET @Pos = CHARINDEX(@Del, @List)		
	END
	
	--Add the last item to the table (if not empty string) 
	IF LEN(LTRIM(@List)) > 0
		INSERT @ListTable (Item) VALUES (LTRIM(@List))

	RETURN 
END

Oskar Austegard 
<a href="http://mo.notono.us">http://mo.notono.us</a>

    

Tags:
• List
• monotonous
• SQL
• udf