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
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
Did you have a chance to try the proposed solutions?
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.
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
is there any other way without creating numbers table here?
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
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 |
+----+--------------+---------+
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:
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.
13 people are following this question.