Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Aug 28 '12 at 11:00

This question came from our site for professional and enthusiast programmers.

    
Oh yes, my bad. Can a moderator move this post please? –  YvesR Aug 28 '12 at 9:06
    
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) –  mehdi lotfi Aug 28 '12 at 10:53
1  
Here is a couple of alternatives evaluated for you. Split strings the right way – or the next best way –  Mikael Eriksson Aug 28 '12 at 11:06
    
For some interesting ways to approach this problem you might want to check this post: dba.stackexchange.com/questions/11506/… –  Max Vernon Aug 28 '12 at 14:33
    
@MikaelEriksson great link. Put this as answer, so I can upvote it. –  YvesR Aug 28 '12 at 16:56

3 Answers 3

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
share|improve this answer
    
Thank you. Will do and compare the performance. –  YvesR Aug 28 '12 at 9:22

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: http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco/314917#314917

share|improve this answer

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
share|improve this answer
2  
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. –  glampert Mar 4 at 15:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.