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

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?

>Solution :

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.