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