CREATE PROCEDURE dbts_piece @string varchar(8000), @delimiter char(1), @piece int, @rtext varchar(8000) OUTPUT AS

-- proc:	sp_piece
-- desc:	returns the nth delimted piece of a text string
-- params:	@string		- delimited text string to parse
--		@delimiter	- text string delimiter character
--		@piece		- the nth delimited piece of the text string to return
-- returns:	@rtext		- nth delimited piece of the text string
-- author:	Keith R. Davis / Mutiny Bay Software
-- notes:		

IF CHARINDEX(@delimiter, @string, 1) = 0 
BEGIN
	IF @piece = 1
		SET @rtext = @string
	ELSE
		SET @rtext = ''
	RETURN
END

IF CAST(@string AS varchar) = @delimiter
BEGIN
	SET @rtext = ''
	RETURN
END

IF @piece = 1
BEGIN
	SET @rtext = SUBSTRING(@string, 1, CHARINDEX(@delimiter, @string, 1) - 1)
	RETURN
END

WHILE @piece > 1
BEGIN
	IF CHARINDEX(@delimiter, @string, 1) = 0
	BEGIN
		SET @rtext = ''
		RETURN
	END

  	SET @string = SUBSTRING(@string,  CHARINDEX(@delimiter, @string, 1) + 1, LEN(@string) - CHARINDEX(@delimiter, @string, 1))
	SET @piece = @piece - 1	

	EXEC dbts_piece @string, @delimiter, 1, @rtext OUTPUT
END

RETURN