您可以通过递归CTE解决此问题
DECLARE @tbl TABLE (Case_Reference NVARCHAr(MAX),Narrative NVARCHAr(MAX));INSERT INTO @tbl VALUES (N'C1',N'01/02/2000 Some text with blanks 02/03/2000 More text 03/04/2000 An even more'),(N'C2',N'01/02/2000 Test for C2 02/03/2000 One more for C2 03/04/2000 An even more 04/05/2000 Blah'),(N'C3',N'01/02/2000 Test for C3 02/03/2000 One more for C3 03/04/2000 An even more') ;WITH recCTE AS( SELECt 1 AS Step,Case_Reference,Narrative,CAST(1 AS BIGINT) AS StartsAt,NewPos.EndsAt+10 AS EndsAt,LEN(Narrative) AS MaxLen ,SUBSTRINg(Narrative,NewPos.EndsAt+10+1,999999) AS RestString FROM @tbl AS tbl CROSS APPLY(SELECt PATINDEX('%[0-3][0-9]/[0-1][0-9]/[1-2][0-9][0-9][0-9] %',SUBSTRINg(Narrative,12,9999999))) AS NewPos(EndsAt) UNIOn ALL SELECT r.Step+1,r.Case_Reference,r.Narrative,r.EndsAt+1,CASE WHEN NewPos.EndsAt>0 THEN r.EndsAt+NewPos.EndsAt+10 ELSE r.MaxLen END,r.MaxLen ,SUBSTRINg(r.RestString,NewPos.EndsAt+10+1,999999) FROM recCTE AS r CROSS APPLY(SELECt PATINDEX('%[0-3][0-9]/[0-1][0-9]/[1-2][0-9][0-9][0-9] %',SUBSTRINg(r.RestString,12,99999999))) AS NewPos(EndsAt) WHERe r.EndsAt<r.MaxLen)SELECT Step,Case_Reference,StartsAt,EndsAt ,SUBSTRINg(Narrative,StartsAt,EndsAt-StartsAt+1) AS OutputString FROM recCTEORDER BY Case_Reference,Step结果
+------+----------------+----------+--------+---------------------------------------+| Step | Case_Reference | StartsAt | EndsAt | OutputString |+------+----------------+----------+--------+---------------------------------------+| 1 | C1 | 1 | 38 | 01/02/2000 Some text with blanks |+------+----------------+----------+--------+---------------------------------------+| 2 | C1 | 39 | 60 | 02/03/2000 More text |+------+----------------+----------+--------+---------------------------------------+| 3 | C1 | 61 | 84 | 03/04/2000 An even more |+------+----------------+----------+--------+---------------------------------------+| 1 | C2 | 1 | 24 | 01/02/2000 Test for C2 |+------+----------------+----------+--------+---------------------------------------+| 2 | C2 | 25 | 52 | 02/03/2000 One more for C2|+------+----------------+----------+--------+---------------------------------------+| 3 | C2 | 53 | 77 | 03/04/2000 An even more |+------+----------------+----------+--------+---------------------------------------+| 4 | C2 | 78 | 93 | 04/05/2000 Blah|+------+----------------+----------+--------+---------------------------------------+| 1 | C3 | 1 | 24 | 01/02/2000 Test for C3 |+------+----------------+----------+--------+---------------------------------------+| 2 | C3 | 25 | 52 | 02/03/2000 One more for C3|+------+----------------+----------+--------+---------------------------------------+| 3 | C3 | 53 | 76 | 03/04/2000 An even more |+------+----------------+----------+--------+---------------------------------------+
