It's usually best to do this kink of processing in a front end language with better string processing capabilities than SQL has. But if you want to do this in SQL, then
Declare @MyData Table(ExampleNbr varchar(10), MyData varchar(50));
Insert @MyData(ExampleNbr, MyData) Values
('Example 1', '(CA1G1, 1) (CG31, 1) (CF7, 1) (FD, 1)'),
('Example 2', '(CA1G1, 1) (CG31, 1)'),
('Example 3', '(CG31, 1) (CF7, 1) (FD, 1)');
;With cteReplace As
(Select ExampleNbr, Replace(Replace(MyData, '(', ''), ')', ',') As MyDataR
From @MyData),
cteSplit As
(Select *, s.value As SplitString
From cteReplace
Cross Apply String_split(MyDataR, ',') As s)
Select ExampleNbr, Sum(Try_Cast(SplitString As int)) As Total
From cteSplit
Group By ExampleNbr;
Tom