'Sum of digits in string SQL

Suppose I have column with strings with digits and non-digits. For example 1a2b~#3c. How can I find sum of the digits in these strings. I can't use CTE and my query should begin with SELECT

sqlsql-server


Solution 1:[1]

Using an inline numbers table (use a permanent table if possible though with as many rows as the largest possible string), I would solve this as follows:

declare @string varchar(50)='1a24b~0#3c1'

select Sum(n) 
from (select @string s)s
cross apply(
    select Try_Convert(int,Substring(s,p,1))
    from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15))x(p)
    where p <= Len(s) and Ascii(Substring(s,p,1)) between 49 and 57
)x(n)

Solution 2:[2]

Here is my version. Looks pretty similar to the one Stu posted.

select sum(try_convert(int, MyNum))
from
(
    select MyNum = substring(x.y, t.n, 1)
    from (values('1a2b~#3c'))x(y)
    join (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))t(n) on t.n <= len(x.y)
) t

If you are on an older version of sql server before try_convert you could modify this slightly to something like this.

select sum(convert(int, MyNum))
from
(
    select MyNum = substring(x.y, t.n, 1)
    from (values('1a2b~#3c'))x(y)
    join (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))t(n) on t.n <= len(x.y)
    where substring(x.y, t.n, 1) like '[0-9]'
) t

Solution 3:[3]

And here's the non-boring version using STRING_SPLIT. Of course for "non-boring" you may read "overengineered" as well, depending on your preference.

declare @s varchar(50) = '1a2b~#3c';

select sum(c)
from (values ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')) v(n)
cross apply (
    select c = count(*) * n - n 
    from string_split(@string, n)
) _

Unlike the other solutions, the VALUES clause here is not a tally table and it will work for strings of any length (though it becomes fantastically inefficient for long strings with many digits, but that's almost certainly true of any solution that has to stick to pure T-SQL). If CROSS APPLY and/or VALUES are considered too advanced, it's also trivial to rewrite this as a sum of 10 repeated clauses.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stu
Solution 2 Sean Lange
Solution 3 Jeroen Mostert

Related Questions