SQL - receiving multiple patterns from a string

Question

Lets say I have a 2 column table, in 1st col. is an account number and in the 2nd personal client ID BUT in a long string. In that string may be few different clients IDs and each begins with "ID"

|account_no  | note
|12345       | AAAAID1111BBBBID2222CCACAID3333JDBSTGDid4444

How can I receive such result each in single row?:

account_no|ID
12345      1111
12345      2222
...
1234       4444

using regexp_substr(upper(note), 'ID[[:digit:]]{4}') it returns me only first ID from the whole string.

Any loop or is there any trick with regexps?

Answer 1

Use CONNECT BY to traverse through the string, looking for case-insensitive matches of the letters 'ID' followed by 1 or more digits optionally followed by the end of the line in order to catch the last one. When found, get the second grouping which is the number part of the match as indicated by surrounding with parenthesis.

with tbl(account_no, note) as (
  select 12345, 'AAAAID1111BBBBID2222CCACAID3333JDBSTGDid4444' from dual
)
select account_no, 
       regexp_substr(note, '(ID(\d+))$?', 1, level, 'i', 2) ID
from tbl
connect by level <= regexp_count(note, 'ID\d+', 1, 'i');