SQL oracle 中 Like 与 Contains() 之间的搜索条件差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5700795/
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
search criteria difference between Like vs Contains() in oracle
提问by narsireddy
I created a table with two columns.I inserted two rows.
我创建了一个包含两列的表格。我插入了两行。
id name
1 narsi reddy
2 narei sia
one is simply number type and another one is CLOB type.So i decided to use indexing on that. I queried on that by using contains. query:
一个是简单的数字类型，另一个是 CLOB 类型。所以我决定使用索引。我通过使用 contains 来查询。询问：
select * from emp where contains(name,'%a%e%')>0
2 narei sia
I expected 2 would come,but not. But if i give same with like it's given what i wanted. query:
我预计 2 会来，但不会。但如果我给予同样的，就像它给予我想要的一样。询问：
select * from emp where name like '%a%e%'
ID NAME
1 (CLOB) narsi reddy
2 (CLOB) narei sia
2 rows selected
finally i understood that like is searching whole document or paragraph but contains is looking in words.
最后我明白了喜欢搜索整个文档或段落，但包含正在寻找文字。
so how can i get required output?
那么我怎样才能获得所需的输出呢？
回答by Jeffrey Kemp
LIKE and CONTAINS are fundamentally different methods for searching.
LIKE 和 CONTAINS 是根本不同的搜索方法。
LIKEis a very simple string pattern matcher - it recognises two wildcards (%) and (_) which match zero-or-more, or exactly-one, character respectively. In your case, %a%e% matches two records in your table - it looks for zero or more characters followed by a, followed by zero or more characters followed by e, followed by zero or more characters. It is also very simplistic in its return value: it either returns "matched" or "not matched" - no shades of grey.
LIKE是一个非常简单的字符串模式匹配器——它识别两个通配符 (%) 和 (_)，它们分别匹配零个或多个或恰好一个字符。在您的情况下， %a%e% 匹配表中的两条记录 - 它查找零个或多个字符，后跟a，然后是零个或多个字符，然后是e，然后是零个或多个字符。它的返回值也非常简单：它要么返回“匹配”或“不匹配”——没有灰色阴影。
CONTAINSis a powerful search tool that uses a context index, which builds a kind of word tree which can be searched using the CONTAINS search syntax. It can be used to search for a single word, a combination of words, and has a rich syntax of its own, such as boolean operators (AND, NEAR, ACCUM). It is also more powerful in that instead of returning a simple "matched" or "not matched", it returns a "score", which can be used to rank results in order of relevance; e.g. CONTAINS(col, 'dog NEAR cat') will return a higher score for a document where those two words are both found close together.
CONTAINS是一个强大的搜索工具，它使用上下文索引，它构建了一种可以使用 CONTAINS 搜索语法进行搜索的词树。它可用于搜索单个单词、单词组合，并拥有丰富的自身语法，例如布尔运算符（AND、NEAR、ACCUM）。它也更强大，它不是返回简单的“匹配”或“不匹配”，而是返回一个“分数”，可用于按相关性顺序对结果进行排名；例如 CONTAINS(col, 'dog NEAR cat') 将返回一个更高的分数，用于发现这两个词都靠得很近的文档。
回答by Dave Costa
I believe that your CONTAINSquery is matching 'narei sia' because the pattern '%a%e%' matches the word 'narei'. It does not match against 'narsi reddy' because neither word, taken individually, matches the pattern.
我相信您的CONTAINS查询匹配 'narei sia'，因为模式 '%a%e%' 匹配单词 'narei'。它与 'narsi reddy' 不匹配，因为单独使用这两个词都不匹配该模式。
I assume you want to use CONTAINSinstead of LIKEfor performance reasons. I am not by any means an expert on CONTAINSquery expressions, but I don't see a simple way to do the exact search you want, since you are looking for letters that can be in the same word or different words, but must occur in a given order. I think it may be best to do a combination of the two techniques:
我假设您想使用CONTAINS而不是LIKE出于性能原因。我绝不是CONTAINS查询表达式方面的专家，但我没有看到一种简单的方法来进行您想要的精确搜索，因为您正在寻找可以在同一个词或不同词中的字母，但必须出现在给定的命令。我认为最好将这两种技术结合起来：
WHERE CONTAINS(name,'%a% AND %e%') > 0
AND name LIKE '%a%e%'
I think this would allow the text index to be used to find candidate matches (anything which has at least one word containing 'a' and at least one word containing 'e'). These would would then be filtered by the LIKEcondition, enforcing the requirement that 'a' precede 'e' in the string.
我认为这将允许使用文本索引来查找候选匹配项（任何包含至少一个包含“a”的单词和至少一个包含“e”的单词的内容）。然后这些将被LIKE条件过滤，强制要求在字符串中 'a' 在 'e' 之前。
