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}');
you can try this query out.
Result:
A good explanation of the last two lines can be found here