我想搜索具有逗号分隔数据且条件为in的单元格。例如,如果列值为'AAA,CCC,BBB和WHERE IN('AAA','BBB'(条件,我想搜索相应的行。有没有一种有用的方法可以在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
您可以使用:而不是使用IN
SELECT *
FROM table_name
WHERE ','||column2||',' LIKE '%,AAA,%'
OR ','||column2||',' LIKE '%,BBB,%'
或者,一个表集合表达式:
SELECT *
FROM table_name t
WHERE EXISTS (
SELECT 1
FROM TABLE(SYS.ODCIVARCHAR2LIST('AAA','BBB'))
WHERE ','||t.column2||',' LIKE '%,'||COLUMN_VALUE||',%'
);
对于样本数据:
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;
两种输出:
COLUMN1|COLUMN2:------|:----------数据1|AAA、CCC、BBBdata2|AAA、CCCdata3|BBB,DDD,EEE
db<gt;小提琴这里
您还可以将regexp_substr与exists运算符一起使用:
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)
您可以使用cte作为搜索子查询,并按如下方式使用like
:
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 || ',%'