Skip to content
Advertisement

presto sql filter part of the string java

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 ?

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement