Add values after character

Question

I would like to sum all values between the comma and the right parenthesis in a string of text. Example 1: (CA1G1, 1) (CG31, 1) (CF7, 1) (FD, 1) Result: 4 Example 2: (CA1G1, 1) (CG31, 1) Result: 2 Example 3: (CG31, 1) (CF7, 1) (FD, 1) Result: 3 Thank you.

Answer 1

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

Answer 2

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;

Answer 3

H @jaskiew63 ,

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 |  
+----+---------+  

Answer 4

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.