Split string by SIZE on delimiter

Transact-SQL
1

Say I have a sting:
1000304031, 1000305911, 1000307980, 1000308721, 1000308731, 1000308734, 1000308760, 1000308761, 1000266538, 1000274598, 1000275493

I want to brake it up to a string no longer than 40 characters but brake on comma (four rows)
1000304031, 1000305911, 1000307980
1000308721, 1000308731, 1000308734
1000308760, 1000308761, 1000266538
1000274598, 1000275493

If I do a search I find how to break a delimited string I get ways to do split ON the delimiter (personally I use DelimitedSplit8K - thank you Jeff).

That is not what I want. (Note that my actual application size is 2000 which includes a prefix.)

We are currently doing this is a while loop which is then used in a cursor. I would like to get rid of both!

2

Find last comma before the 40 character limit and break on that.

Is this such a big job that LOOP will be a killer?

What if any single "piece" is more than 40 characters?

3

The loop is not that bad but the cursor needs replaced and as it calls the sp with the loop I was thinking I would replace both.
As to the character limit, the loop takes up to the limit then puts the rest in the next row.

I will look into finding the last delimiter and get back if I have problems. Thank you

4

This is really not pretty, but it works for your sample data:

declare @thestring varchar(2000)='1000304031, 1000305911, 1000307980, 1000308721, 1000308731, 1000308734, 1000308760, 1000308761, 1000266538, 1000274598, 1000275493';
declare @thedelimiter char(1)=',';
declare @thelimit int=40;

with cte(rn,thestring,therest)
  as (select 1
            ,case
                when len(@thestring)>@thelimit
                then left(@thestring,@thelimit-charindex(@thedelimiter,reverse(left(@thestring,@thelimit))))
                else @thestring
             end
            ,case
                when len(@thestring)>@thelimit
                then ltrim(right(@thestring,len(@thestring)-((@thelimit+1)-charindex(@thedelimiter,reverse(left(@thestring,@thelimit))))))
                else null
             end
      union all
      select rn+1
            ,case
                when len(therest)>@thelimit
                then left(therest,@thelimit-charindex(@thedelimiter,reverse(left(therest,@thelimit))))
                else therest
             end
            ,case
                when len(therest)>@thelimit
                then ltrim(right(therest,len(therest)-((@thelimit+1)-charindex(@thedelimiter,reverse(left(therest,@thelimit))))))
                else null
             end
        from cte
       where therest is not null
     )
select thestring
  from cte
 order by rn
;

Hope this gets you started

2 Likes
5

Thank you, I will study it but at first glance it looks like what I need. I have yet to master cte's so do not think like this.