question

jaskiew63-6977 avatar image
0 Votes"
jaskiew63-6977 asked EchoLiu-msft commented

Add values after character

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.

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any update?
Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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;


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered

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 |
 +----+---------+
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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.








5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.