在 Oracle IN 子句中使用 LIKE

2021-12-06 00:00:00 sql sql-like oracle

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

I know I can write a query that will return all rows that contain any number of values in a given column, like so:

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

但是，如果 val1 可以出现在 my_col 中的任何位置，它的数据类型为 varchar(300)，我可能会改为:

but if val1, for example, can appear anywhere in my_col, which has datatype varchar(300), I might instead write:

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

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

Is there a way of combing these two techniques. I need to search for some 30 possible values that may appear anywhere in the free-form text of the column.

以下列方式组合这两个语句似乎行不通:

Combining these two statements in the following ways does not seem to work:

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

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

推荐答案

这里有用的是 PostgreSQL 中可用的 LIKE ANY 谓词

What would be useful here would be a LIKE ANY predicate as is available in PostgreSQL

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

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

Unfortunately, that syntax is not available in Oracle. You can expand the quantified comparison predicate using OR, however:

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

或者，使用 EXISTS 谓词和 辅助数组数据结构创建半连接(请参阅此详情的问题):

Or alternatively, create a semi join using an EXISTS predicate and an auxiliary array data structure (see this question for details):

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

For true full-text search, you might want to look at Oracle Text: http://www.oracle.com/technetwork/database/enterprise-edition/index-098492.html

相关文章