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