Split User Defined Function (updated)

IF exists (SELECT * from dbo.sysobjects 
	WHERE id = object_id(N'[dbo].[Split]') 
	AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[Split]
GO


GO
CREATE FUNCTION dbo.Split (	@vcDelimitedString 		varchar(8000),
				@vcDelimiter			varchar(100) )
/**************************************************************************
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:
	WHO		DATE		DESCRIPTION
	---		----------	---------------------------------------------------
	KMG		01/30/2009	Changed LEN() to DATALENGTH()
***************************************************************************/
RETURNS @tblArray TABLE 
   (
	ElementID	smallint	IDENTITY(1,1),  --Array index
   	Element		varchar(1000)			--Array element contents
   )
AS
BEGIN

	DECLARE 
	@siIndex					smallint,
	@siStart					smallint,
	@siDelSize					smallint


	SET @siDelSize	= DATALENGTH(@vcDelimiter)
	--loop through source string and add elements to destination table array
	WHILE DATALENGTH(@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 , DATALENGTH(@vcDelimitedString) - @siStart + 1)
		END
	END
	
	RETURN
END
GO