By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #TestData
(
ClientID int,
Notes varchar(8000)
)
insert into #TestData
(
ClientID,
Notes
)
select
1,
'Request Notes: VAR - abc abc abc abc abc'
union all
select
2,
'Request Notes: OZR - abc abc abc abc abc Request Notes: ACC - abc abc abc abc abc Request Notes: TYU - abc abc abc abc abc'
union all
select
3,
'Request Notes: TYU - abc abc abc abc abc Request Notes: VAR - abc abc abc abc abc'
3 rows affected
select d.ClientId, n.*
from #testdata d
cross apply (
select Left(j.[value],3) [Type], Row_Number() over(order by Convert(int,j.[key])) [Order]
from OpenJson(Concat('["',replace(notes,'Notes: ', '","'),'"]')) j
where j.[value] != 'Request'
)n;
| ClientId | Type | Order |
|---|---|---|
| 1 | VAR | 1 |
| 2 | OZR | 1 |
| 2 | ACC | 2 |
| 2 | TYU | 3 |
| 3 | TYU | 1 |
| 3 | VAR | 2 |