Sql server 2016 split after 5th comma

Question

I need to split the following string

10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29

to

10, 11, 12, 13, 14
15, 16, 17, 18, 19
20, 21, 22, 23, 24
25, 26, 27, 28, 29

will it always be this format? two numbers then a comma then a space? — S3S, Jun 09 '17 at 13:40
So you want to take a delimited string and split it into multiple delimited strings? Using delimited string in the first place is a poor design choice. This just screams of things gone wrong. — Sean Lange, Jun 09 '17 at 14:35

Dean Savović · Accepted Answer · 2017-06-09T14:31:47.523

1

create function dbo.SplitString (@string varchar(max), @delimiter char(1), @Occurence int)
returns @t table
(
    String varchar(max)
)
as

begin
declare @i int = 0
    , @k int = 1
    , @j int = 0
    , @str varchar(max);

if right(@string, 1) <> ','
    set @string = @string + ',';

while CHARINDEX(@delimiter, @string, @i + 1) > 0
begin
    set @i = CHARINDEX(@delimiter, @string, @i + 1);
    set @j = @j + 1;

    if @j = @Occurence or CHARINDEX(@delimiter, @string, @i + 1) = 0
    begin
        insert into @t (String)
        select SUBSTRING (@string, @k, @i - @k);

        set @k = @i + 1;
        set @j = 0;
    end


end

return;

end

select *
from dbo.SplitString ('10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29', ',', 5);

edited Jun 09 '17 at 14:31

answered Jun 09 '17 at 14:08

Dean Savović

739
3
7

5

Of all the splitters out there this is the absolute worst for performance. It is a multi-statement table valued function using loops inside. Here are several options that are MUCH better. http://sqlperformance.com/2012/07/t-sql-queries/split-strings Since we are wanting a specific position within the results I would prefer a different splitter. The one from Jeff Moden. http://www.sqlservercentral.com/articles/Tally+Table/72993/ It has the advantage of returning the position that none of the splitters do. – Sean Lange Jun 09 '17 at 14:10
If we split into 6, the last two value is not showing – ram kumar Jun 09 '17 at 14:14
@ramkumar added functionality for Occurence = 6 or whatewer – Dean Savović Jun 09 '17 at 14:32
@SeanLange, I absolutely agree with you. – Dean Savović Jun 09 '17 at 14:32

score 0 · Answer 2 · answered Jun 09 '17 at 14:08

There are similar answers to this - try the following link: SQL Server - find nth occurrence in a string

What you're looking to do is find the Nth occurrence of a substring. I believe that I used the accepted answer in the aforementioned link to solve this when I had this issue.

score 0 · Answer 3 · answered Jun 09 '17 at 14:27

Another solution which is not so great but its still in set operation instead of loop as below:

Declare @str varchar(100) = '10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25'

;With cte as
    (
    select RowN = Row_Number() over (order by (SELECT NULL)), *, SplitValue= Case When (Row_Number() over (order by (Select NULL)) % 5 = 0 ) then [Value]+ '$' else [Value] end 
    from string_Split(@str, ',')
    ) 
    Select * from string_split( Stuff((Select ', ' + SplitValue from cte for xml path('')),1,1,''), '$')

Sql server 2016 split after 5th comma

3 Answers3