SQL 在没有 LIKE 的情况下检查 Oracle 中字符串中的子字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1571957/
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
Check for a substring in a string in Oracle without LIKE
提问by Richard Knop
How can I check for a substring in a string in Oracle without using LIKE? Let's say I want to select all users from a table that have the letter "z" in their last name:
如何在不使用 LIKE 的情况下检查 Oracle 中字符串中的子字符串？假设我想从一个表中选择姓氏中包含字母“z”的所有用户：
SELECT * FROM users WHERE last_name LIKE "%z%";
That would work, but I don't want to use LIKE. Is there some other function I could use?
那行得通，但我不想使用 LIKE。我可以使用其他一些功能吗？
采纳答案by wefwfwefwe
I'm guessing the reason you're asking is performance? There's the instrfunction. But that's likely to work pretty much the same behind the scenes.
我猜你问的原因是性能？有instr函数。但这在幕后的工作方式可能几乎相同。
Maybe you could look into full text search.
也许你可以看看全文搜索。
As last resorts you'd be looking at caching or precomputed columns/an indexed view.
作为最后的手段，您将查看缓存或预先计算的列/索引视图。
回答by Dave Costa
If you were only interested in 'z', you could create a function-based index.
如果您只对 'z' 感兴趣，您可以创建一个基于函数的索引。
CREATE INDEX users_z_idx ON users (INSTR(last_name,'z'))
Then your query would use WHERE INSTR(last_name,'z') > 0.
那么您的查询将使用WHERE INSTR(last_name,'z') > 0.
With this approach you would have to create a separate index for each character you might want to search for. I suppose if this is something you do often, it might be worth creating one index for each letter.
使用这种方法，您必须为您可能想要搜索的每个字符创建一个单独的索引。我想如果这是你经常做的事情，那么为每个字母创建一个索引可能是值得的。
Also, keep in mind that if your data has the names capitalized in the standard way (e.g., "Zaxxon"), then both your example and mine would not match names that begin with a Z. You can correct for this by including LOWER in the search expression: INSTR(LOWER(last_name),'z').
另外，请记住，如果您的数据的名称以标准方式大写（例如，“Zaxxon”），那么您的示例和我的示例都不会匹配以 Z 开头的名称。您可以通过在搜索表达式： INSTR(LOWER(last_name),'z').
回答by darreljnz
You can do it this way using INSTR:
您可以使用 INSTR 以这种方式执行此操作：
SELECT * FROM users WHERE INSTR(LOWER(last_name), 'z') > 0;
INSTR returns zero if the substring is not in the string.
如果子字符串不在字符串中，则 INSTR 返回零。
Out of interest, why don't you want to use like?
出于兴趣，你为什么不想使用like？
Edit: I took the liberty of making the search case insensitive so you don't miss Bob Zebidee. :-)
编辑：我冒昧地使搜索不区分大小写，因此您不会错过 Bob Zebidee。:-)
回答by Foxfire
Databases are heavily optimized for common usage scenarios (and LIKE is one of those).
数据库针对常见的使用场景进行了大量优化（LIKE 就是其中之一）。
You won't find a faster way of doing your search if you want to stay on the DB-level.
如果您想停留在数据库级别，您将找不到更快的搜索方式。
回答by David Aldridge
Bear in mind that it is only worth using anything other than a full table scan to find these values if the number of blocks that contain a row that matches the predicate is significantly smaller than the total number of blocks in the table. That is why Oracle will often decline the use of an index in order to full scan when you use LIKE '%x%' where x is a very small string. For example if the optimizer believes that using an index would still require single-block reads on (say) 20% of the table blocks then a full table scan is probably a better option than an index scan.
请记住，只有当包含与谓词匹配的行的块数明显小于表中块的总数时，才值得使用全表扫描以外的任何其他方法来查找这些值。这就是为什么当您使用 LIKE '%x%' 其中 x 是一个非常小的字符串时，Oracle 通常会拒绝使用索引以进行完整扫描。例如，如果优化器认为使用索引仍然需要对（比如说）20% 的表块进行单块读取，那么全表扫描可能比索引扫描更好。
Sometimes you know that your predicate is much more selective than the optimizer can estimate. In such a case you can look into supplying an optimizer hint to perform an index fast full scan on the relevant column (particularly if the index is a much smaller segment than the table).
有时您知道您的谓词比优化器可以估计的更具选择性。在这种情况下，您可以考虑提供优化器提示以对相关列执行索引快速全扫描（特别是如果索引的段比表小得多）。
SELECT /*+ index_ffs(users (users.last_name)) */
*
FROM users
WHERE last_name LIKE "%z%"
