I have a difficult task in TSQL that I can't seem to find a simple way to do. I am trying to use CROSS APPLY STRING_SPLIT(sentence, ' '), but I can only get one word to the method. Can you please help? Thank you.

Sample sentence:

1. I need to split strings using TSQL.
2. This approach is traditional, and is supported in all versions and editions of SQL Server.

Desired answer:

1. I need
2. to split
3. strings using
4. TSQL.

Desired Answer:

1. This approach
2. is traditional
3. , and
4. is supported
5. in all
6. versions and
7. editions of
8. SQL Server.

CodePudding user response：

Here you go:

First add a space to any comma (you want a comma treated as a word), then split the string on each space into rows using some Json, then assign groups to pair each row using modulo and lag over(), then aggregate based on the groups:

declare @s varchar(100)='This approach is traditional, and is supported in all versions and editions of SQL Server';

select Result = String_Agg(string,' ') within group (order by seq)
from (
  select j.[value] string, Iif(j.[key] % 2 = 1, Lag(seq) over(order by seq) ,seq) gp, seq
  from OpenJson(Concat('["',replace(Replace(@s,',',' ,'), ' ', '","'), '"]')) j
  cross apply(values(Convert(tinyint,j.[key])))x(seq)
)x
group by gp;

Result:

See Demo Fiddle