sqlsql-serversql-server-2016-express

Sql server 2016 split after 5th comma


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

Solution

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