Oracle 精确查询逗号分隔的字段 原创

Oracle 精确查询逗号分隔的字段

-- 需求: 表里的某个字段存储的值是以逗号分隔开来的，要求根据分隔的每一个值都能查出来数据，但是不能使用like查询。
-- 场景：一个流程节点tk_pid，可以配置多个规则，多个规则的ID以逗号分隔存在一个字段conf_ids
-- 查询场景1：查询一个规则是否被使用？

复制收展SQLselect *
from (
         select id, tk_pid, regexp_substr(conf_ids, '[^,]+', 1, level) conf_id
         from (
                  select 1 as id, '001' as tk_pid, '1,2,3' as conf_ids
                  from dual
                  union all
                  select 2 as id, '002' as tk_pid, '1' as conf_ids
                  from dual
                  union all
                  select 3 as id, '003' as tk_pid, '1,2' as conf_ids
                  from dual
                  union all
                  select 4 as id, '003' as tk_pid, '' as conf_ids
                  from dual
              )
         connect by level <= regexp_count(conf_ids, ',') + 1
                and tk_pid = prior tk_pid
                and prior dbms_random.value is not null
     )
where 1 = 1
  and conf_id = '1';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22