Split

CREATE FUNCTION dbo.Split (@vcDelimitedString varchar(8000),
   @vcDelimitervarchar(1))

/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points.  Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data

PARAMETERS:
@vcDelimitedString- The string to be split
@vcDelimiter- String containing the delimiter where
  delimited string should be split

RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string

USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') 
ORDER BY ElementID

AUTHOR:Karen Gayda

DATE: 05/31/2001

MODIFICATION HISTORY:
WHODATEDESCRIPTION
----------------------------------------------------------------

***************************************************************************/RETURNS @tblArray TABLE 
   (
ElementIDsmallintIDENTITY(1,1),  --Array index
   Elementvarchar(1000)--Array element contents
   )
AS
BEGIN

DECLARE 
@siIndexsmallint,
@siStartsmallint,
@siDelSizesmallint


SET QUOTED_IDENTIFIER ON

SET @siDelSize= LEN(@vcDelimiter)

--loop through source string and add elements to destination table array

WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END

RETURN
END