CODEWITHSUNDEEP
sqloracle-databaseplsql
1line
1line

Reputation: 13

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

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

Upvotes: 0

Views: 881

Answers (3)

MT0
MT0

Reputation: 167972

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

Upvotes: 1

tomash
tomash

Reputation: 21

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)

Upvotes: 1

Popeye
Popeye

Reputation: 35900

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 || ',%'

Upvotes: 0

Related Questions