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 p

Solution 1:

you can try this query out.

with test as(
  select'ABC12345, DE22222'as JOB_Description from DUAL unionselect'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
CONNECTBY LEVEL <= REGEXP_COUNT(JOB_Description, '(ABC|DE)([[:digit:]]){5}')
  AND PRIOR JOB_Description = JOB_Description
  AND PRIOR DBMS_RANDOM.VALUE ISNOTNULL

Result:

ABC12345
DE22222
ABC12345
DE22222

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