在oracle数据库中以逗号分隔的字符串列中搜索字符串

Question

我的oracle数据库中有一个逗号分隔的字符串列(allow_zones(，如下所示：

--------------------------
| id | name | allow_zones|
| 1  | test | 1,23,44,67 |
| 2  | user | 3,33,4,97  |
| 3  | sam  | 9,77,34,6  |
| 4  | kate | 2,83,49,69 |
--------------------------

现在我想检查一个字符串是否假定23存在于列allow_zones上。我似乎不知道该怎么做。

Answer 1

一个简单的例子可能是：

样本数据：

SQL> with test (id, name, allow_zones) as
2    (select 1, 'test', '1,23,44, 67' from dual union all
3     select 2, 'user', '3,33,4, 97'  from dual union all
4     select 5, 'mike', '1234,5,6'    from dual union all
5     select 6, 'tige', '23,52,4'     from dual union all
6     select 7, 'scot', '0,15,123'    from dual union all
7     select 8, 'king', '124,23'      from dual
8    )

获取包含,23,的行(如果不是字面意义上的，则制造(：

9  select *
10  from test
11  where ',' || allow_zones ||',' like '%,' || 23 || ',%';
ID NAME ALLOW_ZONES
---------- ---- -----------
1 test 1,23,44, 67
6 tige 23,52,4
8 king 124,23

---

或者，您可以

<snip>
9  -- first split allow_zones into rows ...
10  temp as
11    (select regexp_substr(allow_zones, '[^,]+', 1, column_value) val,
12            id, name, allow_zones
13     from test cross join
14     table(cast(multiset(select level from dual
15                         connect by level <= regexp_count(allow_zones, ',') + 1
16                        ) as sys.odcinumberlist))
17    )
18  -- ... then fetch those that contain 23
19  select id, name, allow_zones
20  from temp
21  where val = '23';
ID NAME ALLOW_ZONES
---------- ---- -----------
1 test 1,23,44, 67
6 tige 23,52,4
8 king 124,23
SQL>

Answer 2

检查目标值的前面是字符串的开头或逗号，后面是逗号或字符串的结尾。

with test (id, name, allow_zones) as (
select 1, 'test', '1,23,44, 67' from dual union all
select 2, 'user', '3,33,4, 97'  from dual union all
select 5, 'mike', '1234,5,6'    from dual union all
select 6, 'tige', '23,52,4'     from dual union all
select 7, 'scot', '0,15,123'    from dual union all
select 8, 'king', '124,23'      from dual
)
select *
from test
where regexp_like(allow_zones, '(^|,)23(,|$)');

ID NAME ALLOW_ZONES
---------- ---- -----------
1 test 1,23,44, 67
6 tige 23,52,4    
8 king 124,23     
3 rows selected.

Answer 3

您可以使用|将WHERE语句中的列名用'、'括起来，这样每行值的开头和结尾都会添加逗号，然后您可以用逗号将它们与LIKE声明进行匹配，这样您就可以匹配"23〃"；23"；，23〃和"；，23：

SELECT * FROM mytable
WHERE ',' || allow_zones ||',' LIKE '%,23,%'

或者，您也可以在搜索中使用regex，但该查询可能会更慢