Split Comma Delimited String

Andy has put together a really good video on Comma Delimited String parsing in sql. Personally I am a big fan of the CLR functionality on this issue. Make sure to check out the video he has put together.

Link to video:
http://www.sqlshare.com/SplittingDelimitedStrings_774.aspx

Link to Andy's example:
http://www.sqlshare.com/references.774.zip

Here is another example of the split function:

GO
/****** Object: UserDefinedFunction [dbo].[fn_Split] Script Date: 06/02/2010 17:05:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_Split]
(
@sText VARCHAR(8000) ,
@sDelim VARCHAR(20) = ' '
)
RETURNS @retArray TABLE
(
idx SMALLINT PRIMARY KEY ,
value VARCHAR(8000)
)
AS
BEGIN
DECLARE
@idx SMALLINT ,
@value VARCHAR(8000) ,
@bcontinue BIT ,
@iStrike SMALLINT ,
@iDelimlength TINYINT

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTRIM(RTRIM(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ( ( @iDelimlength = 0 )
OR ( @sDelim = 'Empty' )
)
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText) > 0
BEGIN
SET @value = SUBSTRING(@sText, 1,
CHARINDEX(@sDelim,
@sText) - 1)
BEGIN
INSERT @retArray
( idx, value )
VALUES
( @idx, @value )
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value)
+ @iDelimlength
SET @idx = @idx + 1
SET @sText = LTRIM(RIGHT(@sText,
DATALENGTH(@sText)
- @iStrike))

END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray
( idx, value )
VALUES
( @idx, @value )
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue = 1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText) > 1
BEGIN
SET @value = SUBSTRING(@sText, 1, 1)
BEGIN
INSERT @retArray
( idx, value )
VALUES
( @idx, @value )
END
SET @idx = @idx + 1
SET @sText = SUBSTRING(@sText, 2,
DATALENGTH(@sText) - 1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray
( idx, value )
VALUES
( @idx, @sText )
SET @bcontinue = 0
END
END

END

RETURN
END