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

• YvesR: Oh yes, my bad. Can a moderator move this post please?
• mehdi lotfi: You can use below link: 1- SQL User Defined Function to Parse a Delimited String [2- INSERT INTO TABLE from comma separated varchar-list ](stackoverflow.com/a/6354838/1407421)
• Mikael Eriksson: Here is a couple of alternatives evaluated for you. Split strings the right way – or the next best way
• Hannah Vernon: For some interesting ways to approach this problem you might want to check this post: dba.stackexchange.com/questions/11506/…
• YvesR: @MikaelEriksson great link. Put this as answer, so I can upvote it.

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

• glampert: A plain code dump is not an ideal answer. Please explain why your implementation is better/faster/smaller and which improvement were made in relation to the OP.