question

cooolsweet-5113 avatar image
0 Votes"
cooolsweet-5113 asked EchoLiu-msft commented

tsql : sum of string

hi

i have strings like this

123432453212
1dddddddddd
1gh45j2dungj


what i need is sum of each numbers and ignore characters or replace it with 0 for sum



example : 123432453212 = 32
1dddddddddd = 1
1gh45j2dungj = 12



any help



sql-server-transact-sql
· 3
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.

@cooolsweet-5113,

What is your SQL Server version?

 SELECT @@VERSION;
0 Votes 0 ·

@cooolsweet-5113,

Did you have a chance to try the proposed solutions?

0 Votes 0 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered
DECLARE @str varchar(20) = '1234abc99cc'
SELECT SUM(try_cast(substring(@str, n, 1) AS int))
FROM   Numbers
WHERE  n BETWEEN 1 AND len(@str)

Numbers here is a one-column table with rows from 1, 2, 3 etc. See this article of mine how to create it and what other use you may have of it: https://www.sommarskog.se/Short%20Stories/table-of-numbers.html

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.

cooolsweet-5113 avatar image
0 Votes"
cooolsweet-5113 answered Viorel-1 converted comment to answer

is there any other way without creating numbers table here?

· 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.

Indeed there is. I discuss those alternatives in my article.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

If you are not interested in writing queries only, then consider a loop:

 declare @string varchar(max) = '1gh45j2dungj'
    
 declare @sum int = 0
 declare @i int = len(@string)
    
 while @i > 0
 begin
     set @sum += isnull(try_cast(substring(@string, @i, 1) as int), 0)
     set @i -= 1
 end
    
 select @sum

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 YitzhakKhabinsky-0887 edited

Hi @cooolsweet-5113,

Please try the following solution.

SQL

 -- DDL and sample data population, start
 DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(1024));
 INSERT INTO @tbl (tokens) VALUES
 ('123432453212'),
 ('1dddddddddd'),
 ('1gh45j2dungj'),
 ('fafa');
 -- DDL and sample data population, end
    
 SELECT p.ID, p.tokens, (
     SELECT SUBSTRING(tokens, number, 1)
     FROM @tbl AS c
         CROSS APPLY (
             SELECT DISTINCT number
             FROM master..spt_values
             WHERE number BETWEEN 1 AND LEN(tokens)
         ) V
     WHERE c.ID = p.ID
     FOR XML PATH('r'), TYPE, ROOT('root')
 ).value('sum(/root/r/text()) cast as xs:unsignedLong?', 'INT') AS Summary
 FROM @tbl AS p;

Output

 +----+--------------+---------+
 | ID |    tokens    | Summary |
 +----+--------------+---------+
 |  1 | 123432453212 |      32 |
 |  2 | 1dddddddddd  |       1 |
 |  3 | 1gh45j2dungj |      12 |
 |  4 | fafa         |       0 |
 +----+--------------+---------+
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 @cooolsweet-5113,

Viorel-1's method looks great. I modified it into a function, which is more convenient to use, please refer to:

 CREATE TABLE #test(string varchar(25))
 INSERT INTO #test VALUES('123432453212'),('1dddddddddd'),('1gh45j2dungj')
     
  create  function [dbo].[Getsum](@UDF02 varchar(40)) returns int
  as
  begin
  declare @string varchar(max)
  set @string =@UDF02
  declare @sum int = 0
  declare @i int = len(@string)
        
  while @i > 0
  begin
      set @sum += isnull(try_cast(substring(@string, @i, 1) as int), 0)
      set @i -= 1
   end
    
 return @sum
 end
    
 SELECT *,[dbo].[Getsum](string) 
 FROM #test
    
 DROP FUNCTION [dbo].[Getsum]

Output:
112799-image.png

This answer should have appeared in the comment, but due to the character limitation of the comment, it appears here.


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.



image.png (3.5 KiB)
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.