Split Delimiter Separated Lists

From:http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-29-udf_Txt_SplitTAB.htm

Creating and parsing lists of items in a string is a frequent
request on SQL Server discussion groups. Storing multiple items
in a list runs counter to relational concepts such as
normalization. However, it's a compact format that most people
find easy to read and that many other programs use for data
exchange.

Here's an example of what I'm talking about. It's a comma-
separated list of the Three Stooges:

'Moe, Larry, Shemp, Curly, Joe, Curly-Joe'

Why 6 stooges? Shemp didn't really like getting poked in the
eye and retired. He was replaced by Curly, Joe, and then
Curly-Joe. They kept on making Stooges shorts into the 1960s.

Once you have a list in a string, it's often necessary to split
it into the individual items. That's the purpose of this
week's UDF. Here's the CREATE FUNCTION script:
/---------- Start copying below this line ----------------------\
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.udf_Txt_SplitTAB (

@sInputList varchar(8000) -- List of delimited items
, @Delimiter char(1) = ',' -- delimiter that separates items
) RETURNS @List TABLE (Item varchar(8000))
WITH SCHEMABINDING
/*
* Returns a table of strings that have been split by a delimiter.
* Similar to the Visual Basic (or VBA) SPLIT function. The
* strings are trimmed before being returned. Null items are not
* returned so if there are multiple separators between items,
* only the non-null items are returned.
* Space is not a valid delimiter.
*
* Example:
select * FROM dbo.udf_Txt_SplitTAB('abcd,123, 456, efh,,hi', ',')
*
* Test:
DECLARE @Count int, @Delim char(10), @Input varchar(128)
SELECT @Count = Count(*)
FROM dbo.udf_Txt_SplitTAB('abcd,123, 456', ',')
PRINT 'TEST 1 3 lines:' + CASE WHEN @Count=3
THEN 'Worked' ELSE 'ERROR' END
SELECT @DELIM=CHAR(10)
, @INPUT = 'Line 1' + @delim + 'line 2' + @Delim
SELECT @Count = Count(*)
FROM dbo.udf_Txt_SplitTAB(@Input, @Delim)
PRINT 'TEST 2 LF :' + CASE WHEN @Count=2
THEN 'Worked' ELSE 'ERROR' END
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published in T-SQL UDF of the Week Newsletter Vol 1 #29
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
***************************************************************/
AS BEGIN

DECLARE @Item Varchar(8000)
DECLARE @Pos int -- Current Starting Position
, @NextPos int -- position of next delimiter
, @LenInput int -- length of input
, @LenNext int -- length of next item
, @DelimLen int -- length of the delimiter

SELECT @Pos = 1
, @DelimLen = LEN(@Delimiter) -- usually 1
, @LenInput = LEN(@sInputList)
, @NextPos = CharIndex(@Delimiter, @sInputList, 1)

-- Doesn't work for space as a delimiter
IF @Delimiter = ' ' BEGIN
INSERT INTO @List
SELECT 'ERROR: Blank is not a valid delimiter'
RETURN
END


-- loop over the input, until the last delimiter.
While @Pos <= @LenInput and @NextPos > 0 BEGIN

IF @NextPos > @Pos BEGIN -- another delimiter found
SET @LenNext = @NextPos - @Pos
Set @Item = LTrim(RTrim(
substring(@sInputList
, @Pos
, @LenNext)
)
)
IF LEN(@Item) > 0
Insert Into @List Select @Item
-- ENDIF

END -- IF

-- Position over the next item
SELECT @Pos = @NextPos + @DelimLen
, @NextPos = CharIndex(@Delimiter
, @sInputList
, @Pos)
END

-- Now there might be one more item left
SET @Item = LTrim(RTrim(
SUBSTRING(@sInputList
, @Pos
, @LenInput-@Pos + 1)
)
)

IF Len(@Item) > 0 -- Put the last item in, if found
INSERT INTO @List SELECT @Item

RETURN
END
GO

GRANT SELECT ON [dbo].[udf_Txt_SplitTAB] to PUBLIC
GO
\---------- Stop copying above this line -----------------------/

Now, lets break up the Stooges:
/---------- Start copying below this line ----------------------\
SELECT Item as [Stooge]
FROM udf_Txt_SplitTAB(
'Moe, Larry, Shemp, Curly, Joe, Curly-Joe'
, ',')
\---------- Stop copying above this line -----------------------/
(Results)
Stooge
----------------------------------------
Moe
Larry
Shemp
Curly
Joe
Curly-Joe

Of course, you can use other separators if you like. However,
udf_Txt_SplitTAB can't use space as a delimiter. Spaces get
special treatment, they're stripped from the ends of the items.
This next statement illustrates:

/---------- Start copying below this line ----------------------\
SELECT '->' + Item + '<-' as [Item]
FROM udf_Txt_SplitTAB(
'1, 22 , 333, 4444 , , 55555'
, ',')
\---------- Stop copying above this line -----------------------/
(Results)
Item
----------------------------------------
->1<-
->22<-
->333<-
->4444<-
->55555<-

Next issue I'll show you how to pull of the reverse trick and
combine multiple strings into a comma separated list.