-- =============================================
-- Author:		Frank Bazan
-- Create date: 13 Feb 2007
-- Description:	Takes a String and tokenises it
-- =============================================

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_tokenizeString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_tokenizeString]
GO

CREATE FUNCTION fn_tokenizeString
(
	-- Params
	@Id INT,
	@CSV NVARCHAR(MAX),
	@Delimiter CHAR(1)
)
RETURNS 
@WordList TABLE 
(
	-- Columns
	ID INT, 
	Word NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @String NVARCHAR(MAX),
		@Word	NVARCHAR(MAX)

	-- A clean string to work with
	SET	@String	= REPLACE(@CSV + @Delimiter,' ','')

	-- loop through string inserting one word at a time into table
	WHILE CHARINDEX(@Delimiter,@String) > 0
	BEGIN
		SELECT @Word = LEFT(@String,CHARINDEX(@Delimiter,@String))
		SELECT @String = RIGHT(@String,LEN(@String)-LEN(@Word))
			IF LEN(REPLACE(@Word,@Delimiter,'')) > 0
			BEGIN
				INSERT INTO @WordList
				VALUES (@Id, REPLACE(@Word,@Delimiter,''))
			END
	END	
	RETURN 
END
GO