SQL Server Split function

Question

I know there are some answers about a user function to split chars and I have one already running (found one years ago in the net and modified it to my own needs).

Since I use this function very often, I'd like to ask the best SQL professionals (which are probably here) to review this function for performance. Is the function ok or is there a faster or better way to do this now? Minimum requirements are SQL Server 2005 to work, but if there are better ways from 2008 I can roll out two versions (most usage of the DB is 2008, only a few 2005 left).

CREATE FUNCTION [dbo].[fn_Split](@text nvarchar(4000), @delimiter char(1) = ',')

RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value nvarchar(4000)
)

AS
BEGIN

DECLARE @index int
SET @index = -1

SET @text = RTRIM(LTRIM(@text))

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
    SET @text = RIGHT(@text, (LEN(@text) - @index))
  END
  RETURN
END

Answer 1

try this:

Create function dbo.SplitString(@inputStr varchar(1000),@del varchar(5))
RETURNS @table TABLE(col varchar(100))
As
BEGIN

DECLARE @t table(col1 varchar(100))
INSERT INTO @t
select @inputStr

if CHARINDEX(@del,@inputStr,1) > 0
BEGIN
    ;WITH CTE1 as (
    select ltrim(rtrim(LEFT(col1,CHARINDEX(@del,col1,1)-1))) as col,RIGHT(col1,LEN(col1)-CHARINDEX(@del,col1,1)) as rem from @t
    union all
    select ltrim(rtrim(LEFT(rem,CHARINDEX(@del,rem,1)-1))) as col,RIGHT(rem,LEN(rem)-CHARINDEX(@del,rem,1))
    from CTE1 c
    where CHARINDEX(@del,rem,1)>0
    )

        INSERT INTO @table 
        select col from CTE1
        union all
        select rem from CTE1 where CHARINDEX(@del,rem,1)=0
    END
ELSE
BEGIN
    INSERT INTO @table 
    select col1 from @t
END

RETURN

END

Answer 2

ADD:

Found a new one which is around 4 times faster then my one :)

CREATE FUNCTION [dbo].[fn_Split] (@text VARCHAR(MAX), @delimiter VARCHAR(32) = ',')

RETURNS @t TABLE ( [position] INT IDENTITY PRIMARY KEY, [value] VARCHAR(MAX) )   
AS
BEGIN
    DECLARE @xml XML
    SET @XML = N'<root><r>' + REPLACE(@text, @delimiter, '</r><r>') + '</r></root>'

    INSERT INTO @t([value])
    SELECT r.value('.','VARCHAR(MAX)') as Item
    FROM @xml.nodes('//root/r') AS RECORDS(r)

    RETURN
END

Modified it to my needs, but taken from second answer from this link: https://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco/314917#314917

Answer 3

From my tests this function is faster than yours fn_split() and SplitString().

CREATE FUNCTION [dbo].[fn_split](
       @input VARCHAR(8000),
       @delimiter VARCHAR(10) = ';'
    ) 
    RETURNS @result TABLE (rownum int NOT NULL, item VARCHAR(max))

    BEGIN
    DECLARE @item VARCHAR(max), @rownum int = 0

    WHILE CHARINDEX(@delimiter,@input,0) > 0
    BEGIN
        SELECT
            @item = ltrim(RTRIM(LTRIM(SUBSTRING(@input, 1, CHARINDEX(@delimiter, @input, 0) - 1)))),
            @input = ltrim(RTRIM(LTRIM(SUBSTRING(@input, CHARINDEX(@delimiter, @input, 0) + LEN(@delimiter), LEN(@input)))))

        IF LEN(@item) > 0
        BEGIN
            SET @rownum += 1;
            INSERT INTO @result SELECT @rownum, @item
        END
    END

    IF LEN(@input) > 0
    BEGIN
        SET @rownum += 1;
        INSERT INTO @result SELECT @rownum, @input
    END
    RETURN
END
GO