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),
@vcDelimitervarchar(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:
WHODATEDESCRIPTION
----------------------------------------------------------------
KMG01/30/2009Changed LEN() to DATALENGTH()
***************************************************************************/RETURNS @tblArray TABLE 
   (
ElementIDsmallintIDENTITY(1,1),  --Array index
   Elementvarchar(1000)--Array element contents
   )
AS
BEGIN

DECLARE 
@siIndexsmallint,
@siStartsmallint,
@siDelSizesmallint


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