presto sql filter part of the string java

Question

I have a string in col1 select col1 from table

"blah blah 123 blah iterate 866 loss blah blah"

how to filter string between iterate and loss and just show sum of all such filtered numbers ?

Answer 1

You can use regexp_extract to extract the number within that string, convert the values to a numeric type and sum them up:

WITH t(v) AS (
    VALUES 'blah blah 123 blah iterate 866 loss blah blah'
)
SELECT sum(CAST(regexp_extract(v, 'iterate (\d+) loss', 1) AS bigint)) 
FROM t