Sql Server Split Function

Here is the split function that splits the given varchar with the given dilimiteer




CREATE FUNCTION [dbo].[ufn_Split](@text varchar(8000), @delimiter
varchar(20) = ' ')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
BEGIN
--You can uncomment the below statement if you want to insert nulls
--INSERT INTO @Strings VALUES (NULL)
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
END
RETURN
END