Sum multiple regular expressions in SQL

I have a string column with values that I extract using regex. The pattern is: d{2}/d{2}/d{4}-([d,.]+)

There are multiple values that match that pattern and I would like to extract and sum all of them in Oracle SQL.

For example: “text 100 text 200 text”. Result: 300.

Thank you!

Answer

You can use CONNECT BY LEVEL Statement :

WITH t2 AS
(
  SELECT DISTINCT ID, regexp_substr(str,'[0-9]+',1,level) AS NMR
    FROM t
 CONNECT BY level <= regexp_count(str,'[0-9]+')
)
SELECT SUM(NVL(NMR,0)) AS "Numeric Result"
  FROM t2

together with regexp_substr() and regexp_count() functions assuming you have a primary key column(ID)

If you need row-wise aggregation, then consider using GROUP BY ID for the main query :

SELECT ID, SUM(NVL(NMR,0)) AS "Numeric Result"
  FROM t2
 GROUP BY ID 

Demo