如何查找oracle中的特定值

Question

i有一些值在我的表，像这样，column150,52,53,54,56,57,58,60,61,63,75 50,53,54 54,75

我想找到包含53,54返回50,52,53,54,56,57,58,60,61,63,75 50,53,54行

或50,52,53返回50,52,53,54,56,57,58,60,61,63,75

我试着;WHERE Regexp_like(DIST_GRP,('53,54]'))

Answer 1

您可以搜索列中的每个数字，并使用AND组合过滤器:

SELECT *
FROM   table_name
WHERE  ','||column1||',' LIKE '%,54,%'
AND    ','||column1||',' LIKE '%,75,%'

或

SELECT *
FROM   table_name
WHERE  ','||column1||',' LIKE '%,50,%'
AND    ','||column1||',' LIKE '%,52,%'
AND    ','||column1||',' LIKE '%,53,%'

或者您可以拆分字符串并计数以确保您有所有匹配:

SELECT t.*
FROM   table_name t
CROSS APPLY (
SELECT 1
FROM   DUAL
WHERE  REGEXP_SUBSTR(t.column1, 'd+', 1, LEVEL) IN (50, 52, 53)
CONNECT BY LEVEL <= REGEXP_COUNT(t.column1, 'd+')
HAVING COUNT(DISTINCT REGEXP_SUBSTR(t.column1, 'd+', 1, LEVEL)) = 3
)

或者您可以创建一个集合数据类型并拆分字符串并重新聚合为:

CREATE TYPE int_list AS TABLE OF INT;

然后

SELECT t.*
FROM   table_name t
CROSS APPLY (
SELECT CAST(
COLLECT(
TO_NUMBER(REGEXP_SUBSTR(t.column1, 'd+', 1, LEVEL))
)
AS INT_LIST
) AS list
FROM   DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(t.column1, 'd+')
) l
WHERE  INT_LIST(50,52,53) SUBMULTISET OF l.list;

db<此处小提琴>