tsql : sum of string

Question

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

Answer 1

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

Answer 2

is there any other way without creating numbers table here?

Answer 3

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

Answer 4

Hi @coool sweet ,

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

Answer 5

Hi @coool sweet ,

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:

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.