oracle get all matched occurrences from a column

问题:

I have a table, which has 2 columns: ID & JOB_Description(Text). I would like to write an oracle SQL to extract all substrings in the Description column which match a regular pattern.

However, I have learnt how to extract matched substrings from a string with below SQL, but I have no idea to apply below SQL on all data in one go on the aforementioned table(column:JOB_Description).

SQL to get all matched occurrences from a string:

 SELECT REGEXP_SUBSTR(JOB_Description, '(ABC|DE)([[:digit:]]){5}', 1, LEVEL) AS substr
   FROM (
         select 'Please help to repair ABC12345, DE22222' as JOB_Description 
          from DUAL)
  CONNECT BY LEVEL <= REGEXP_COUNT(JOB_Description, '(ABC|DE)([[:digit:]]){5}');

回答1:

you can try this query out.

 with test as(
  select 'ABC12345, DE22222' as JOB_Description from DUAL union
  select 'Please help to repair ABC12345, DE22222' as JOB_Description from DUAL 
)
SELECT REGEXP_SUBSTR(JOB_Description, '(ABC|DE)([[:digit:]]){5}', 1, LEVEL) AS substr
FROM test
CONNECT BY LEVEL <= REGEXP_COUNT(JOB_Description, '(ABC|DE)([[:digit:]]){5}')
  AND PRIOR JOB_Description = JOB_Description
  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

Result:

ABC12345
DE22222
ABC12345
DE22222

A good explanation of the last two lines can be found here