Split Delimited String (Updated: Multi char delimeters)

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnSplit]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnSplit]
GO

/*
' Created by tcartwright / The System Shop Inc.
' Date: 11-14-2002    Time: 17:27
*/
CREATE     FUNCTION fnSplit(@Data nvarchar(4000), 
@Delimiter varchar(10) = ',')
RETURNS @tblSplit TABLE 
(ItemId int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
Item nvarchar(4000) NULL)
AS
BEGIN
DECLARE @Delimiter2 varchar(12),
@item nvarchar(4000),
@iPos int,
@DelimWidth int

--had to do this cuz if they send in a > 9 char delimiter I could not pre and post append the % wildcards
SET @Delimiter2 = @Delimiter 
SET @Delimiter2 = ISNULL(@Delimiter2, ',')
SET @DelimWidth = LEN(@Delimiter2) 

IF RIGHT(RTRIM(@Data), 1) <> @Delimiter2      
SELECT @Data = RTRIM(@Data) + @Delimiter2

IF LEFT(@Delimiter2, 1) <> '%' 
SET @Delimiter2 = '%' + @Delimiter2

IF RIGHT(@Delimiter2, 1) <> '%' 
SET @Delimiter2 = @Delimiter2 + '%'

SELECT @iPos = PATINDEX(@Delimiter2, @Data) 

WHILE @iPos > 0
BEGIN 
SELECT @item = LTRIM(RTRIM(LEFT(@Data, @iPos - 1)))
IF @@ERROR <> 0 BREAK
SELECT @Data =  RIGHT(@Data, LEN(@Data) - (LEN(@item) + @DelimWidth))
IF @@ERROR <> 0 BREAK

INSERT INTO @tblSplit VALUES(@item)
IF @@ERROR <> 0 BREAK

SELECT @iPos = PATINDEX(@Delimiter2, @Data) 
IF @@ERROR <> 0 BREAK
END

RETURN 
END



GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO