Hi @jaskiew63 ,
Welcome to microsoft TSQL Q&A forum!
Please refer to:
declare @test table (id int,string varchar(100));
insert into @test values
(1, '(CA1G1, 1) (CG31, 1) (CF7, 1) (FD, 1)'),
(2, '(CA1G1, 1) (CG31, 1) '),
(3, '(CG31, 1) (CF7, 1) (FD, 1)')
;with cte
as(select id,v.value from @test cross apply string_split(string,',') v)
,cte2 as(select id,left(value,2) value from cte
where value not like '(%')
select distinct id,sum(cast(value as int)) over(partition by id order by id) result from cte2
If you have any question, please feel free to let me know.
Regards
Echo
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.