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

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)

CodePudding user response：

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.

CodePudding user response：

Don't split the string (it is an expensive operation and is unnecessary); instead look for a matching sub-string (including leading and trailing delimiters so that you match entire terms):

SELECT full_cert_name
FROM   tbl_fullofcerts c
WHERE  EXISTS(
  SELECT 1
  FROM   table_name t
  WHERE  ';' || t.pre_purposetypeid || ';' LIKE  '%;' || c.certid || ';%'
);

SELECT c.certid,
       c.full_cert_name,
       t.pre_purposetypeid
FROM   tbl_fullofcerts c
       INNER JOIN table_name t
       ON ';' || t.pre_purposetypeid || ';' LIKE  '%;' || c.certid || ';%';