SQL Summing digits of a number

Question

i'm using presto. I have an ID field which is numeric. I want a column that adds up the digits within the id. So if ID=1234, I want a column that outputs 10 i.e 1+2+3+4.

I could use substring to extract each digit and sum it but is there a function I can use or simpler way?

Answer 1

You can combine regexp_extract_all from @akuhn's answer with lambda support recently added to Presto. That way you don't need to unnest. The code would be really self explanatory if not the need for cast to and from varchar:

presto> select
    reduce(
        regexp_extract_all(cast(x as varchar), '\d'), -- split into digits array
        0, -- initial reduction element
        (s, x) -> s + cast(x as integer), -- reduction function
        s -> s -- finalization
    ) sum_of_digits
from (values 1234) t(x);

 sum_of_digits
---------------
            10
(1 row)