question

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

In case there is a number instead of string in the first part inside the parenthesis, you can try this. Please note that your SQL server must be 2016 or higher to support the system function STRING_SPLIT().

 DECLARE @Table TABLE (
     RowId int,
     MyString varchar(100)
 );
 INSERT INTO @Table VALUES
 (1, '(CA1G1, 1) (CG31, 1) (CF7, 1) (FD, 1)'),
 (2, '(CA1G1, 1) (CG31, 1) '),
 (3, '(CG31, 1) (CF7, 1) (FD, 1)'),
 (4, '(1, 1) (CF7, 1) (FD, 1)');
    
 SELECT t.RowId, c.NumberCount
 FROM @Table AS t
 CROSS APPLY (
     SELECT SUM(TRY_CAST(SUBSTRING(value, CHARINDEX(', ', value) + 2, LEN(value) - CHARINDEX(',', value)) AS int)) AS NumberCount
     FROM STRING_SPLIT(REPLACE(REPLACE(t.MyString, ') (', '|'), ')', ''), '|')
 ) AS c;

H @jaskiew63-6977,

You data resembles JSON. Here is a solution for SQL Server 2016 onwards.

SQL

 -- DDL and sample data population, start
 DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, MyString NVARCHAR(MAX));
 INSERT INTO @tbl (MyString) VALUES
 ('(CA1G1, 1) (CG31, 1) (CF7, 1) (FD, 1)'),
 ('(CA1G1, 1) (CG31, 1) '),
 ('(CG31, 1) (CF7, 1) (FD, 1)'),
 ('(1, 1) (CF7, 18) (FD, 10)');
 -- DDL and sample data population, end
    
 ;WITH rs AS
 (
     SELECT * 
         , REPLACE(REPLACE(REPLACE(REPLACE(MyString
             , ', ', '":"')
             , ') (', '","')
             , '(', '{"')
             , ')', '"}') AS js
     FROM @tbl
 )
 SELECT ID, SUM(TRY_CAST(value AS INT)) AS summary
 FROM rs 
     CROSS APPLY OPENJSON(js)
 GROUP BY rs.ID;

Output

 +----+---------+
 | ID | summary |
 +----+---------+
 |  1 |       4 |
 |  2 |       2 |
 |  3 |       3 |
 |  4 |      29 |
 +----+---------+

Hi @jaskiew63-6977，

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.