sql - 2个逗号分隔字符串的Oracle SQL交集

Question

我可以应用什么功能？

询问：

Select x, f(y) from table where y like '%ab%cd%ef';

示例表（y按字母顺序排序）

x.  y
1   ab
2   ab,cd
3   cd,ef
4   ab,ef,gh,yu
5   de,ef,rt

预期输出：

输出：

x y
1 ab
2 ab,cd
3 cd,ef
4 ab,ef
5 ef

标签： sqlregexoracle

Answer 1

使用regexp_substr带有connect by level表达式的函数作为

with tab(x,y) as
(
 select 1,'ab'          from dual union all
 select 2,'ab,cd'       from dual union all
 select 3,'cd,ef'       from dual union all 
 select 4,'ab,ef,gh,yu' from dual union all 
 select 5,'de,ef,rt'    from dual
), tab2 as
( 
Select x, regexp_substr(y,'[^,]+',1,level) as y 
  from tab  
 connect by level <= regexp_count(y,',') + 1 
     and prior x = x
     and prior sys_guid() is not null
), tab3 as
(
select x, y  
  from tab2     
 where y like '%ab%'
    or y like '%cd%'
    or y like '%ef%'
)
select x, listagg(y,',') within group (order by y) as y 
  from tab3      
 group by x;

X   Y
1   ab
2   ab,cd
3   cd,ef
4   ab,ef
5   ef

Demo