在 Oracle IN 子句中使用 LIKE

Question

我知道我可以编写一个查询，该查询将返回给定列中包含任意数量值的所有行，如下所示：

Select * from tbl where my_col in (val1, val2, val3,... valn)

但是，例如，如果 val1 可以出现在 my_col 中的任何位置code>，其数据类型为 varchar(300)，我可能会写：

select * from tbl where my_col LIKE '%val1%'

有没有办法结合这两种技术。我需要搜索大约 30 个可能出现在该列的自由格式文本中任何位置的值。

通过以下方式组合这两个语句似乎不起作用：

select * from tbl where my_col LIKE ('%val1%', '%val2%', 'val3%',....) 

select * from tbl where my_col in ('%val1%', '%val2%', 'val3%',....)

Answer 1

这里有用的是 a LIKE PostgreSQL 中可用的任何 谓词

SELECT * 
FROM tbl
WHERE my_col LIKE ANY (ARRAY['%val1%', '%val2%', '%val3%', ...])

不幸的是，该语法在 Oracle 中不可用。但是，您可以使用 OR 扩展量化比较谓词：

SELECT * 
FROM tbl
WHERE my_col LIKE '%val1%' OR my_col LIKE '%val2%' OR my_col LIKE '%val3%', ...

或者，使用 EXISTS 谓词和 辅助数组数据结构（详细信息请参阅此问题）：

SELECT *
FROM tbl t
WHERE EXISTS (
  SELECT 1
  -- Alternatively, store those values in a temp table:
  FROM TABLE (sys.ora_mining_varchar2_nt('%val1%', '%val2%', '%val3%'/*, ...*/))
  WHERE t.my_col LIKE column_value
)

对于真正的全文搜索，您可能需要查看 Oracle Text：http://www.oracle.com/technetwork/database/enterprise-edition/index-098492.html< /a>

Answer 2

REGEXP_LIKE 将执行不区分大小写的正则表达式搜索。

select * from Users where Regexp_Like (User_Name, 'karl|anders|leif','i')

这将作为全表扫描执行 - 就像LIKE或解决方案一样，因此如果表不存在，性能将非常很糟糕小的。如果不经常使用的话可能还好。

如果您需要某种性能，则需要 Oracle Text（或某些外部索引器）。

要使用 Oracle Text 获取子字符串索引，您将需要 CONTEXT 索引。它有点复杂，因为它是为了使用大量智能来索引大型文档和文本而设计的。如果您有特殊需求，例如数字和所有单词中的子字符串搜索（包括“the”“an”“a”，空格等），您需要创建自定义词法分析器来删除一些智能内容...

如果您插入大量数据时，Oracle Text 不会使事情变得更快，特别是如果您需要在事务内更新索引而不是定期更新索引。

Answer 3

不，你不能这样做。 IN 子句中的值必须完全匹配。您可以这样修改选择：

SELECT *
  FROM tbl
 WHERE my_col LIKE %val1%
    OR my_col LIKE %val2%
    OR my_col LIKE %val3%
 ...

如果 val1、val2、val3...足够相似，您也许可以在 REGEXP_LIKE 运算符中使用正则表达式。

Answer 4

在 Oracle 中，您可以按如下方式使用 regexp_like：

select *
from   table_name
where  regexp_like (name, '^(value-1|value-2|value-3....)');

插入符 (^) 运算符表示行首字符 &
管道符(|) 表示OR 运算。

Answer 5

是的，您可以使用此查询（代替 'Specialist' 和 'Developer'，输入您想要用逗号分隔的任何字符串，然后更改 employees表与您的表）

SELECT * FROM employees em
WHERE EXISTS (select 1 from  table(sys.dbms_debug_vc2coll('Specialist', 'Developer')) mt  
              where em.job like ('%' || mt.column_value || '%'));

为什么我的查询比接受的答案更好：您不需要 CREATE TABLE 权限来运行它。只需使用 SELECT 权限即可执行此操作。

Answer 6

这个速度相当快：

select * from listofvalue l 
inner join tbl on tbl.mycol like '%' || l.value || '%'

Answer 7

只是添加@Lukas Eder 的答案。

避免创建表和插入值的改进
（我们可以使用 select from Dual 和 unpivot 来“即时”实现相同的结果）：

with all_likes as  
(select * from 
    (select '%val1%' like_1, '%val2%' like_2, '%val3%' like_3, '%val4%' as like_4, '%val5%' as like_5 from dual)
    unpivot (
     united_columns for subquery_column in ("LIKE_1", "LIKE_2", "LIKE_3", "LIKE_4", "LIKE_5"))
  )
    select * from tbl
    where exists (select 1 from all_likes where tbl.my_col like all_likes.united_columns)

Answer 8

我更喜欢这个，

WHERE CASE WHEN my_col LIKE '%val1%' THEN 1    
           WHEN my_col LIKE '%val2%' THEN 1
           WHEN my_col LIKE '%val3%' THEN 1
           ELSE 0
           END = 1

我并不是说它是最佳的，但它有效并且很容易理解。我的大多数查询都是临时使用一次，因此性能对我来说通常不是问题。

Answer 9

您可以将值放入 ODCIVARCHAR2LIST 中，然后将其作为常规表连接。

select tabl1.* FROM tabl1 LEFT JOIN 
(select column_value txt from table(sys.ODCIVARCHAR2LIST
('%val1%','%val2%','%val3%')
)) Vals ON tabl1.column LIKE Vals.txt WHERE Vals.txt IS NOT NULL

Answer 10

select * from tbl
 where exists (select 1 from all_likes where all_likes.value = substr(tbl.my_col,0, length(tbl.my_col)))

Answer 11

您不需要 https://stackoverflow.com/a/6074261/802058 中提到的集合类型。只需使用子查询：

SELECT *
FROM tbl t
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT 'val1%' AS val FROM dual
        UNION ALL
        SELECT 'val2%' AS val FROM dual
        -- ...
        -- or simply use an subquery here
    )
    WHERE t.my_col LIKE val
)