1

Lets assume 'table1' has three columns:

'key', 'singleID', 'multipleIDs'

Rows would be like:

1,'8736', '1234;6754;9785;6749' 
2,'7446', '9959;7758;6485;9264'

To search for all rows which have an id either in 'singleID' or as part of the concatenated IDs in the 'multipleIDs' I would:

select key from table1 where 
  singleID = '8888' or multipleIDs like '%8888%';

When searching not only for one ID (8888) like in this statement but for 100 it would be necessary to repeate the where clause 100 times with different id like:

select key from table1 where 
  singleID = '8888' or multipleIDs like '%8888%' or
  singleID = '9999' or multipleIDs like '%9999%' or
....;

The IDs to search for are taken dynamically from another query like

 select id from table2;

The query shall be created dynamically since the number of IDs might vary. Like this the SQL statement would become quite long.

Is there a nice and short way to express that in Oracle SQL? PLSQL perhaps?

Spanky
  • 111
  • 9
  • 10
    storing values as csv is very bad db design – Jens Aug 09 '16 at 21:02
  • Sometimes, since we are not living in a perfect world, we have to cope with legacy data and legacy data structures. – Spanky Oct 14 '17 at 07:23
  • @Spanky That's absolutely true, but bad design should still be addressed so that other users who might be beginners don't get wrong ideas about db design. – Modus Tollens Oct 14 '17 at 07:29

2 Answers2

0

Something like this?

This is the test version:

with sv_qry
as
(
SELECT trim(regexp_substr(search_values, '[^,]+', 1, LEVEL)) val
  FROM (select '1234,7446' as search_values
from dual
)
CONNECT BY LEVEL <= regexp_count(search_values, ',')+1
)
, table1_qry
as
(select 1 as id,'8736' as single_id, '1234;6754;9785;6749' as multiple_id from dual
union all
select 2,'7446' as single_id, '9959;7758;6485;9264' as multiple_id from dual
)
select *
from table1_qry
inner join
sv_qry
on  single_id = val or multiple_id like '%'||val||'%'

And this would be with a table called table1:

with sv_qry
as
(
SELECT trim(regexp_substr(search_values, '[^,]+', 1, LEVEL)) val
  FROM (select '1234,7446' as search_values
from dual
)
CONNECT BY LEVEL <= regexp_count(search_values, ',')+1
)
select *
from table1
inner join
sv_qry
on  single_id = val or multiple_id like '%'||val||'%'

Partial credit goes here:

Splitting string into multiple rows in Oracle

Community
  • 1
  • 1
Patrick Marchand
  • 3,405
  • 21
  • 13
0

You can express the query like this :

  select key
    from table1 a
    join ( select id from table2 where id in ('yyyy','xxxx','zzzz',...) b 
    on a.singleId = b.id or a.multipleID like '%'||b.id||'%';
ivanzg
  • 419
  • 4
  • 13