SQL Splitting Single Cell Query Results to use as a list in another Query, Poor DB Design work around

So the main crux of my problem is that the original designer of this DB decided to combine results into a single table cell. I do not have the authority to change the DB Schema. I will eventually have to take this query and change it slightly to work through ColdFusion, but anything to get me on the right track would be very helpful.

I need a way to take these values, split them at the comma, and turn them into a list I can use for another query.

Something like:

Select full_cert_name FROM tbl_fullofcerts
WHERE certid IN --(This is where I need help)

>Solution :

This (a subquery) is one way to split it:

select full_cert_name
from tbl_fullofcerts
where certid in (select regexp_substr(pre_purposetypeid, '[^,]+', 1, column_value)
                 from that_table cross join
                   table(cast(multiset(select level from dual
                                       connect by level <= regexp_count(pre_purposetypeid, ',') + 1
                                      ) as sys.odcinumberlist))
                );

What does it do? For example:

SQL> select * from that_Table;

        ID PRE_P PRE_PURP
---------- ----- --------
         1 8,9   28,35,42
         2 4,5,6 1,2

SQL> select id,
  2         pre_purposetypeid,
  3         regexp_substr(pre_purposetypeid, '[^,]+', 1, column_value)
  4  from that_table cross join
  5    table(cast(multiset(select level from dual
  6                        connect by level <= regexp_count(pre_purposetypeid, ',') + 1
  7                       ) as sys.odcinumberlist))
  8  ;

        ID PRE_P REGEXP_SUBSTR(PRE_PU
---------- ----- --------------------
         1 8,9   8
         1 8,9   9
         2 4,5,6 4
         2 4,5,6 5
         2 4,5,6 6

SQL>

Final code depends on what you really want to do (I didn’t quite understand it from your question because "This is where I need help" isn’t very descriptive), but – that’s the general idea.