PL/SQL Search cell data separated by comma with IN condition

Question

I want to search for cells with comma separated data with in condition. For example, if the column value is'AAA, CCC, BBB and WHERE IN('AAA','BBB') condition, I want to search the corresponding row. Is there a useful way to get the following results in Oracle?

// 1. table data
column1    | column2
data1      | AAA,CCC,BBB
data2      | AAA,CCC
data3      | BBB,DDD,EEE
data4      | EEE,FFF,GGG

// 2. wanted result(WHERE COLUMN2 IN ('AAA', 'BBB'))
column1    | column2
data1      | AAA,CCC,BBB
data2      | AAA,CCC
data3      | BBB,DDD,EEE

Does this answer your question? [Using the "IN" clause with a comma delimited string from the output of a replace() function in Oracle SQL](https://stackoverflow.com/questions/4672545/using-the-in-clause-with-a-comma-delimited-string-from-the-output-of-a-replace) — OldProgrammer, Dec 30 '20 at 01:21
Is this a common operation? If you have a poorly designed data model that stores a comma-separated list rather than a child table or even a nested table, you can't fix the data model, and this is something you need to do with some regularity, simple solutions based on `like` and `regexp_substr` are going to be dirt slow because they can't use indexes on the column you're searching. There are ways around the performance problems but that is generally going to make the SQL rather more complex. — Justin Cave, Dec 30 '20 at 07:16

score 1 · Answer 1 · answered Dec 30 '20 at 06:50

You can also use regexp_substr with exists operator:

 select yt.*
   from your_table yt
  where exists (select regexp_substr(yt.column2, '[^,]+', 1, level)
                  from dual 
                 where regexp_substr(yt.column2, '[^,]+', 1, level) in ('AAA', 'BBB')
            connect by regexp_substr(yt.column2, '[^,]+', 1, level) is not null)

score 1 · Accepted Answer · answered Dec 30 '20 at 11:48

Rather than using IN, you can use:

SELECT *
FROM   table_name
WHERE  ','||column2||',' LIKE '%,AAA,%'
OR     ','||column2||',' LIKE '%,BBB,%'

Or, a table collection expression:

SELECT *
FROM   table_name t
WHERE  EXISTS (
         SELECT 1
         FROM   TABLE(SYS.ODCIVARCHAR2LIST('AAA','BBB'))
         WHERE  ','||t.column2||',' LIKE '%,'||COLUMN_VALUE||',%'
       );

Which, for the sample data:

CREATE TABLE table_name ( column1, column2 ) AS
SELECT 'data1', 'AAA,CCC,BBB' FROM DUAL UNION ALL
SELECT 'data2', 'AAA,CCC'     FROM DUAL UNION ALL
SELECT 'data3', 'BBB,DDD,EEE' FROM DUAL UNION ALL
SELECT 'data4', 'EEE,FFF,GGG' FROM DUAL;

Both output:

COLUMN1 | COLUMN2    
:------ | :----------
data1   | AAA,CCC,BBB
data2   | AAA,CCC    
data3   | BBB,DDD,EEE

db<>fiddle here

Popeye · Answer 3 · 2020-12-30T02:41:05.250

0

You can use the cte as search sub-query and use like as follows:

With search_params as
(Select 'AAA' as p from dual union all
Select 'BBB' as p from dual)
 --
Select distinct t.* 
  From your_table t 
  Join search_params s on ',' || t.column2 || ',' like '%,' || s.p || ',%'

edited Dec 30 '20 at 02:41

answered Dec 30 '20 at 01:31

Popeye

35,427
4
10
31

PL/SQL Search cell data separated by comma with IN condition

3 Answers3