Oracle - Select rows from a table based on ID's stored in a column

I have written a stored procedure to do some data matching. When it completes I update a MATCH_DETAILS column stating that it has matched with an ID but it could have matched with other ID's also.

I would like to write SQL to read from the table based on the list of ID's that I have specified in the MATCH_DETAILS column.

Matched with multiple QSIDs, updating with QSID: 6030066992. Possible QSIDs: 6030066992,1777,

So, my SQL would look for the text "Possible QSIDs: " and then pull the ID's after this.

I tried to write a bit of SQL but it doesn't do what I want, it just returns a string but I need individual ID's to do the overall search on:

select SUBSTR(cd.match_details,
                  INSTR(cd.match_details, 'Possible QSIDs: ', 1) + 16,
                  length(cd.match_details) - 1)
      from scv_client_details cd
     where cd.match_details like 'Matched with multiple QSIDs%'

This just returns this:

6030066992,1777,
3602,3600,
etc...

What I would ideally want is to return them as numbers with one per line I guess:

Anyone have any ideas what is required here?

Cheers

Solution

Here's one option; read comments within code:

Sample data:

SQL> with scv_client_details  (match_details) as
  2    (select 'Matched with multiple QSIDs, updating with QSID: 6030066992. Possible QSIDs: 6030066992,1777,'
  3     from dual
  4     union all
  5     select 'Matched with multiple QSIDs. Yet another example; Possible QSIDs: 12345,6789'
  6     from dual
  7    ),

Query begins here:

  8  temp as
  9    -- return strings that follow "Possible QSIDs:"
 10    (select trim(replace(substr(match_Details, instr(match_details, 'Possible QSIDs:')),
 11                    'Possible QSIDs:',
 12                    null
 13                   )) val
 14     from scv_client_details
 15    ),
 16  temp2 as
 17    -- split previously fetched comma-separated strings to rows. Avoid duplicate rows!
 18    (select regexp_substr(val, '[^,]+', 1, column_value) val
 19     from temp cross join
 20       table(cast(multiset(select level from dual
 21                           connect by level <= regexp_count(val, ',') + 1
 22                          ) as sys.odcinumberlist))
 23    )
 24  -- finally, result is numeric part of previously fetched VAL
 25  select regexp_substr(val, '\d+') result
 26  from temp2
 27  where regexp_like(val, '\d+');

Result:

RESULT
--------------------------------------------------------------------------------
6030066992
1777
12345
6789

SQL>