XML Split And Recursive Concat

XMLSplitAndRecursiveConcat.sql

declare @s varchar(max) = 'Hi my name is M and I have two sister 1) M and 2) K'
 
;with mycte as (
select  Cast(N'<H><r>'+replace(@s ,' ', '</r><r>') + '</r></H>'  AS XML) vals
)
,mycte2 as (
 SELECT ROW_NUMBER() OVER (ORDER BY S.a.value('count(.)', 'tinyint')) rn 
 ,S.a.value('.', 'VARCHAR(100)') AS splitVal  
 FROM mycte d
 CROSS APPLY d.[vals].nodes('/H/r') S(a)
 where S.a.value('.', 'VARCHAR(100)') not like '%[0-9])%'
 )
 , rCTE as (
 select rn, splitVal, splitVal as mergedCol from mycte2
 where rn=1
 union all
  select m.rn, m.splitVal
  , Cast(r.mergedCol +' '+ m.splitVal  as varchar(100)) 
  from mycte2 m join rCTE r on m.rn=r.rn+1
  )

select top 1 mergedCol from rCTE
order by rn desc