1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42



-- 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))