Split on Occurrence

Transact-SQL
1

What is the fastest way to break this up? I would like to split this into a new record or line when it counts every 5th comma?

string:
'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16'

final results:
'1,2,3,4,5'
'6,7,8,9,10'
'11,12,13,14,15'
'16'

2

I'd use a variant of Jeff Moden's DelimitedSplit8k which would be something a bit like:

declare @string varchar(8000) = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16';
declare @count int = len(@string);
declare @delimiter char = ',';
declare @groupsize int = 5;

With E1(N) As (Select 1 From (Values(1),(1),(1),(1),(1)) N(X)),
E2(N) As (Select 1 From E1 Cross Join E1 A Cross Join E1 A2),
E4(N) As (Select 1 From E2 Cross Join E2 A Cross Join E2 A2),
Tally(N) As (Select N From (Select Top(@Count) Row_Number() Over (Order By N) From E4) As T(N)),
Start(N1) As (
	Select 1 
	Union All Select N + 1 From Tally Where Substring(@string, N, 1) = @delimiter
),
GroupData(N1, N2) As (
	Select Min(N1), Max(N1) From
		(Select N1, (ROW_NUMBER() Over (Order By N1) - 1) / @groupsize As Grp From Start) RN
	Group By RN.Grp
),
Length(N1,L1) As (
	Select N1,
		IsNull(NullIf(CharIndex(@delimiter,@string,N2),0) - N1, 8000)
	From GroupData
)
Select  
	Row_number() Over (Order By N1) As ItemNumber,
	SubString(@String, N1, L1) As Item
From Length

If you are on a new enough version of SQL Server you can almost certainly beat this by doing a similar reworking of the DelimitedSplit8K_LEAD variation which uses Window Functions to improve the original string spltting.

2 Likes