01.CREATE FUNCTION [dbo].[fn_Split]
02.(
03. @text nvarchar(max), @delimiter nvarchar(5) = ' '
04.)
05.RETURNS @Strings TABLE
06.(
07. position int IDENTITY PRIMARY KEY,
08. value nvarchar(max)
09.)
10.AS
11.BEGIN
12.
13.DECLARE @index int
14.SET @index = -1
15.
16.WHILE (LEN(@text) > 0)
17. BEGIN
18. SET @index = CHARINDEX(@delimiter , @text)
19. IF (@index = 0) AND (LEN(@text) > 0)
20. BEGIN
21. INSERT INTO @Strings VALUES (@text)
22. BREAK
23. END
24. IF (@index > 1)
25. BEGIN
26. INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
27. SET @text = RIGHT(@text, (LEN(@text) - @index))
28. END
29. ELSE
30. SET @text = RIGHT(@text, (LEN(@text) - @index))
31. END
32. RETURN
33.
34.END
