regex oracle sql return all capturing groups

Question

I have an regex like

select regexp_substr('some stuff TOTAL_SCORE<518>some stuff OTHER_VALUE<456> foo <after>', 'TOTAL_SCORE<(\d{3})>', 1, 1, NULL, 1) from dual which can return a value for a single capturing group. How can I instead return all the capturing groups as an additional column? (string concat of results is fine)

select regexp_substr('some stuff TOTAL_SCORE<518> TOTAL_SCORE<123>some stuff OTHER_VALUE<456> foo <after>', 'TOTAL_SCORE<(\d{3})>') from dual

Answer 1

Query 1:

-- Sample data
WITH your_table ( value ) AS (
  SELECT 'some stuff TOTAL_SCORE<518>some stuff OTHER_VALUE<456> foo <after>' FROM DUAL
)
-- Query
SELECT REGEXP_REPLACE(
         value,
         '.*TOTAL_SCORE<(\d{3})>.*OTHER_VALUE<(\d{3})>.*',
         '\1,\2'
       ) As scores
FROM   your_table

Output:

SCORES
-------
518,456

Query 2:

-- Sample data
WITH your_table ( value ) AS (
  SELECT 'some stuff TOTAL_SCORE<518> TOTAL_SCORE<123> some stuff OTHER_VALUE<456> foo <after>' FROM DUAL
)
-- Query
SELECT l.column_value As scores
FROM   your_table t,
       TABLE(
         CAST(
           MULTISET(
             SELECT TO_NUMBER(
                      REGEXP_SUBSTR(
                        t.value,
                        'TOTAL_SCORE<(\d{3})>',
                        1,
                        LEVEL,
                        NULL,
                        1
                      )
                    )
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT( t.value, 'TOTAL_SCORE<(\d{3})>' ) 
           ) AS SYS.ODCINUMBERLIST
         )
       ) l;

Output:

SCORES
-------
    518
    123