Custom Split with JSON and String_AGG.sql

--SQL Server 2017 or 2019

Create table test (Col1 varchar(900))

Insert into test values


('sql4 13434 22 test 39480'),
('sql2 DEF 39 tests'),
('data 123 258 256 tests 4587'),
('sql2 FED tests')


 ;with mycte as (
select Col1,[value],Cast([key] as int) k,  
case 
when lead([value]) over(partition by Col1 order by [key])  like'%test%'
and  try_cast([value] as int)  is not null 
then 1 
when  [value]  like'%test%'
and lag(try_cast([value] as int)) over(partition by Col1 order by [key]) is  null 
then 1
else 0 end  newkey 
from  TEST
cross apply openjson('["'+(replace(Col1,' ','","')+'"]'))
)

,mycte2 as (
select Col1 ,k, [value]
, Sum(newkey)over(partition by Col1 order by k) grp  
from mycte
 )
 
,mycte3 as (
select Col1,grp,  (case when grp=0 then string_agg([value],' ')  WITHIN GROUP ( ORDER BY k) else null end) col1a
, (case when grp=1 then string_agg([value],' ')  WITHIN GROUP ( ORDER BY k) else null end) col1b
 from mycte2
 group by Col1,grp
 )
 

 select  Col1, max(col1a) col1a , max(col1b) col1b
  from mycte3
 group by Col1 
 order by 1,2
 


 drop table test