How to add up a string of numbers using SQL (BigQuery)?

Question

I have a string of numbers like this:

670000000000100000000000000000000000000000000000000000000000000

I want to add up these numbers which in the above example would result in 14: 6+7+0+...+1+0+...+0+0+0=14

How would I do this in BigQuery?

Answer 1

Yet another [fun] option

create temp function sum_digits(expression string)
returns int64
language js as """
  return eval(expression);
""";
with example as (
  select '670000000000100000000000000000000000000000000000000000000000000' as s
)
select s, sum_digits(regexp_replace(replace(s, '0', ''), r'(\d)', r'+\1')) result
from example    

with output

What it does is -

• first it transform initial long string into shorter one - 671.
• then it transforms it into expression - +6+7+1
• and finally pass it to javascript eval function (unfortunatelly BigQuery does not have [hopefully yet] eval function)

Answer 2

Consider below approach

with example as (
  select '670000000000100000000000000000000000000000000000000000000000000' as s
)
select s, (select sum(cast(num as int64)) from unnest(split(s,'')) num) result
from example       

with output