-- ufn_Alines ---
IF EXISTS (SELECT * FROM   sysobjects WHERE  name = N'ufn_Alines')
	DROP FUNCTION ufn_Alines
GO
CREATE FUNCTION ufn_Alines 
	(@STR VARCHAR(8000),
	 @separator VARCHAR(16)=',') 
RETURNS @TableArray TABLE 
	(ik INT IDENTITY, 
	 Item VARCHAR(128))
AS
/* Splits passed string into items based on the specified separator string
 Parameters:
	@str  - The string to split
	@separator - The separator string ( comma is default)
 Returns table variable with two columns:
   ik int - Item number 	
   Item varchar(128) - Item itself
*/
 
BEGIN
	DECLARE @Item VARCHAR(128), @pos INT
	WHILE DATALENGTH(@STR) > 0
	BEGIN
		SET @pos = CHARINDEX(@separator, @STR)
		IF @pos = 0 
			SET @pos = DATALENGTH(@STR)+1
 
		SET @Item = LEFT(@STR, @pos -1 )
		SET @STR = SUBSTRING(@STR, @pos + DATALENGTH(@separator), 8000)
		INSERT INTO @TableArray (Item) VALUES(@Item)
	END
 
	RETURN
 
END
 
GO
 
-- Sample of using ufn_Alines UDF
SELECT * FROM dbo.ufn_Alines('The quick brown fox jumps over the lazy dog', SPACE(1))