Sum of digits in string SQL

Question

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

`I can't use CTE and my query should begin with SELECT` <-- where do these bizarre and meaningless limitations come from? — Aaron Bertrand, May 13 '22 at 12:48
What version of SQL Server? From 2016 having `STRING_SPLIT` available suggests an easy (if clunky) solution. Without that things just get more ugly overall. — Jeroen Mostert, May 13 '22 at 13:00
I have tried with STRING_SPLIT. Is it possible to split the string with '^[0-9]' separator or to split to single chars? — PrintScreenSys, May 13 '22 at 13:02
You can only split on single characters, not *to* single characters. But that's no problem, because you only have 9 different characters to split on that are relevant, so put those copy-paste skills to work. — Jeroen Mostert, May 13 '22 at 13:03
https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string — Jonas Metzler, May 13 '22 at 13:06
@JeroenMostert, do you mean by 9 different characters digits? So I will get substrings with non-digit characters. Should I subtract them from original string? — PrintScreenSys, May 13 '22 at 13:08
Hint: if the string contains two instances of the digit `9` and you split on `9`, what is the result, and how would you use it to get `18` out of that? — Jeroen Mostert, May 13 '22 at 13:09
This was kind of a fun exercise. I solved it but not using string split. I used a tally table table and substring. — Sean Lange, May 13 '22 at 13:12
Cool once you post your solution I will probably share mine. — Sean Lange, May 13 '22 at 13:14

score 0 · Answer 1 · answered May 13 '22 at 14:10

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)

score 0 · Answer 2 · answered May 13 '22 at 14:29

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

score 0 · Answer 3 · answered May 13 '22 at 16:16

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.

Sum of digits in string SQL

3 Answers3