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 id 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

I know this is not a code question, more a review, but don't know where to post else to ask the pro's.

share|improve this question
    
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
show 1 more comment

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

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

2 Answers

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 comment

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
add comment

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.