SQL 在 Oracle IN 子句中使用 LIKE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6074201/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using LIKE in an Oracle IN clause
提问by DeveloperM
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)
but if val1, for example, can appear anywhere in my_col, which has datatype varchar(300), I might instead write:
但是val1，例如，如果可以出现在my_col数据类型为 varchar(300) 的 中的任何位置，我可能会改写：
select * from tbl where my_col LIKE '%val1%'
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.
有没有办法结合这两种技术。我需要搜索大约 30 个可能的值，这些值可能出现在列的自由格式文本中的任何位置。
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%',....)
回答by Lukas Eder
select * from tbl
where my_col like '%val1%' or my_col like'%val2%' or my_col like '%val3%', ...
But beware, that might be quite slow... Alternatively, you could insert all acceptable values (including %signs) into a table and semi-join that table:
但请注意，这可能会很慢......或者，您可以将所有可接受的值（包括%符号）插入一个表并半连接该表：
select * from tbl
where exists (select 1 from all_likes where tbl.my_col like all_likes.value)
For true full-text search, you might want to look at Oracle Text:
对于真正的全文搜索，您可能需要查看 Oracle Text：
http://www.oracle.com/technetwork/database/enterprise-edition/index-098492.html
http://www.oracle.com/technetwork/database/enterprise-edition/index-098492.html
回答by DCookie
No, you cannot do this. The values in the IN clause must be exact matches. You could modify the select thusly:
不，你不能这样做。IN 子句中的值必须完全匹配。您可以这样修改选择：
SELECT *
FROM tbl
WHERE my_col LIKE %val1%
OR my_col LIKE %val2%
OR my_col LIKE %val3%
...
If the val1, val2, val3... are similar enough, you might be able to use regular expressions in the REGEXP_LIKE operator.
如果 val1、val2、val3... 足够相似，您也许可以在 REGEXP_LIKE 运算符中使用正则表达式。
回答by KarlP
A REGEXP_LIKEwill do a case-insensitive regexp search.
AREGEXP_LIKE将进行不区分大小写的正则表达式搜索。
select * from Users where Regexp_Like (User_Name, 'karl|anders|leif','i')
This will be executed as a full table scan- just as the LIKE orsolution, so the performance will be reallybad if the table is not small. If it's not used often at all, it might be ok.
这将作为全表扫描执行- 就像LIKE or解决方案一样，因此如果表不小，性能将非常糟糕。如果它根本不经常使用，它可能没问题。
If you need some kind of performance, you will need Oracle Text(or some external indexer).
如果您需要某种性能，您将需要Oracle Text（或一些外部索引器）。
To get substring indexing with Oracle Text you will need a CONTEXT index. It's a bit involved as it's made for indexing large documents and text using a lot of smarts. If you have particular needs, such as substring searches in numbers and all words (including "the" "an" "a", spaces, etc) , you need to create custom lexers to remove some of the smart stuff...
要使用 Oracle Text 获得子字符串索引，您需要一个 CONTEXT 索引。它有点复杂，因为它是为使用大量智能索引大型文档和文本而设计的。如果您有特殊需求，例如在数字和所有单词（包括“the”、“an”、“a”、空格等）中进行子字符串搜索，则需要创建自定义词法分析器来删除一些聪明的东西...
If you insert a lot of data, Oracle Text will not make things faster, especially if you need the index to be updated within the transactions and not periodically.
如果您插入大量数据，Oracle Text 不会加快速度，特别是如果您需要在事务内更新索引而不是定期更新。
回答by GabrielBB
Yes, you can use this query (Instead of 'Specialist'and 'Developer', type any strings you want separated by comma and change employeestable with your table)
是的，您可以使用此查询（而不是'Specialist'and '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 || '%'));
Why my query is better than the accepted answer: You don't need a CREATE TABLEpermission to run it. This can be executed with just SELECTpermissions.
为什么我的查询比接受的答案更好：您不需要CREATE TABLE运行它的权限。这可以仅在SELECT权限下执行。
回答by Jean-Philippe Martin
This one is pretty fast :
这是相当快的：
select * from listofvalue l
inner join tbl on tbl.mycol like '%' || l.value || '%'
回答by Vishnu
select * from tbl
where exists (select 1 from all_likes where all_likes.value = substr(tbl.my_col,0, length(tbl.my_col)))
回答by Plirkee
Just to add on @Lukas Eder answer.
只是为了添加@Lukas Eder 的答案。
An improvement to avoid creating tables and inserting values
(we could use select from dualand unpivotto achieve the same result "on the fly"):
避免创建表和插入值的改进（我们可以使用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)
回答by AWOLKiwi
I prefer this
我更喜欢这个
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
I'm not saying it's optimal but it works and it's easily understood. Most of my queries are adhoc used once so performance is generally not an issue for me.
我并不是说它是最佳的，但它有效并且很容易理解。我的大多数查询都是临时使用的，因此性能对我来说通常不是问题。
回答by Dickson katto
You can put your values in ODCIVARCHAR2LIST and then join it as a regular table.
您可以将您的值放在 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
