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

Time:02-03

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.

enter image description here

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 a very 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 || ';%'
);
  • Related