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?

CodePudding user response：

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

CodePudding user response：

Using a UDF:

CREATE TEMP FUNCTION sum_string(data STRING)
RETURNS INT64
LANGUAGE js AS """
  return data.split('').reduce((acc, i) => acc   parseInt(i), 0);
""";

WITH example AS (
  SELECT '670000000000100000000000000000000000000000000000000000000000000' AS s
)
SELECT *, sum_string(s)
FROM example;

returns

s                                                               f0_
670000000000100000000000000000000000000000000000000000000000000 14

This will fail if your string contains non-digit characters though, you might want to some checks/error-handling if that can happen.

CodePudding user response：

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)