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

Answer 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);