SQL 比较 Oracle 中的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/389535/
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
Compare Strings in Oracle
提问by
I need to query a table for values given a string. The table is case sensitive but I want to do a ToLower() in the comparison.
我需要在给定字符串的情况下查询表中的值。该表区分大小写，但我想在比较中执行 ToLower()。
Suppose I have a classes table with the following data.
假设我有一个包含以下数据的类表。
class teacher
-----------------
Mat101 Smith
MAT101 Jones
mat101 Abram
ENG102 Smith
My query should be something like
我的查询应该是这样的
Select teacher From classes where lower(class) = 'math101'
Is this the best way to do the comparison?
这是进行比较的最佳方法吗？
Update
更新
I have no control over the database or the data. I am a read only consumer.
我无法控制数据库或数据。我是只读消费者。
采纳答案by Alkini
No; it would be better to improve the data: create a numeric ID that represents these seemingly meaningless variations of class (and probably an associated lookup table to get the ID). Use the ID column in the where clause and you should be hitting an indexed numeric column.
不; 最好改进数据：创建一个数字 ID，代表这些看似无意义的类变体（可能还有一个相关的查找表来获取 ID）。在 where 子句中使用 ID 列，您应该点击索引数字列。
If that's no an option, consider a function-based index on lower(class).
如果这不是一个选项，请考虑在lower(class) 上使用基于函数的索引。
If that's not an option, and the question of "best" is strictly relative to performance, consider denormalizing and adding a column that contains lower(class), probably populated with a trigger.
如果这不是一个选项，并且“最佳”问题严格地与性能相关，请考虑非规范化并添加一个包含 lower(class) 的列，可能填充了触发器。
If that's not an option, update the data so that it's all lowercase (and take measures to insert/update only lowercase class data).
如果这不是一个选项，请更新数据，使其全部为小写（并采取措施仅插入/更新小写类数据）。
If you can't update the data like that, then the answer is "maybe".
如果您不能像这样更新数据，那么答案是“也许”。
In any case, you can't call it best if you haven't tested indexing of the column.
在任何情况下，如果您还没有测试过该列的索引，就不能称其为最佳。
回答by Eddie Awad
Here is more information about Function-based Indexes (what Dave was referring to above):
以下是有关基于函数的索引的更多信息（戴夫在上面指的是什么）：
回答by Gabe
This method requires that you run 10gr2 or better.
此方法要求您运行 10gr2 或更高版本。
Before altering session:
更改会话之前：
SQL> WITH LETTERS AS
2 (SELECT 'a' LETTER FROM DUAL UNION ALL
3 SELECT 'b' LETTER FROM DUAL UNION ALL
4 SELECT 'A' LETTER FROM DUAL UNION ALL
5 SELECT 'B' LETTER FROM DUAL)
6 SELECT LETTER FROM LETTERS
7 WHERE LETTER = 'A';
L
-
A
SQL>
If you are able to alter your session, you can do the following:
如果您能够更改会话，则可以执行以下操作：
SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;
Session altered.
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
Session altered.
SQL> WITH LETTERS AS
2 (SELECT 'a' LETTER FROM DUAL UNION ALL
3 SELECT 'b' LETTER FROM DUAL UNION ALL
4 SELECT 'A' LETTER FROM DUAL UNION ALL
5 SELECT 'B' LETTER FROM DUAL)
6 SELECT LETTER FROM LETTERS
7 WHERE LETTER = 'A';
L
-
a
A
Altering the session as done above causes the database to sort and compare the upper and lower case version of the same letter as equivalent objects. For more information, please see http://www.orafaq.com/node/91
如上所述更改会话会导致数据库将同一字母的大写和小写版本作为等效对象进行排序和比较。更多信息请参见http://www.orafaq.com/node/91
HTH, Gabe
HTH，加布
回答by Gary Myers
If the variants (Mat101, MAT101, mat101) refer to the same thing, they should have the same identifier.
如果变体 (Mat101, MAT101, mat101) 指代相同的事物，则它们应该具有相同的标识符。
Depending on the volume of data, frequency of updates, queries etc, you may consider replicating the data into a database that you do control with a cleansing/standardisation stage built into the replication.
根据数据量、更新频率、查询等，您可以考虑将数据复制到您通过复制中内置的清理/标准化阶段控制的数据库中。
While you say "I have no control over the database", if you can query you will have an impact over the database. Someone has control of the database, and it may be worth an email/phone call saying that, if they add a lower() index to that column then it will reduce the impact of your queries on the database.
当您说“我无法控制数据库”时，如果您可以查询，您将对数据库产生影响。有人控制了数据库，可能值得发一封电子邮件/电话说，如果他们向该列添加一个 lower() 索引，那么它将减少您的查询对数据库的影响。
Finally, if the variants are simple enough then you could try
最后，如果变体足够简单，那么您可以尝试
Select teacher From classes where class in (lower('mat101'), upper('mat101'), initcap('mat1010')).
回答by Stew S
Since you added that you're a read-only user of the database, the bestmethod is close to what you started with:
由于您添加了您是数据库的只读用户，因此最好的方法与您开始时的方法相近：
Select teacher From classes where lower(class) = LOWER('math101')
Note that I added LOWER() to the input parameter, just to be sure that's lower-case too. Some might call that "belts and suspenders" (aka redudant); I call it good defensive programming.
请注意，我将 LOWER() 添加到输入参数中，只是为了确保它也是小写的。有些人可能会称之为“腰带和吊带”（又名冗余）；我称之为良好的防御性编程。
回答by Dave Costa
The downside of the kind of query you are talking about is that it cannot use an index on class (as an index lookup, that is -- it could still be used in a fast full scan of the index).
您所谈论的这种查询的缺点是它不能在类上使用索引（作为索引查找，也就是说 - 它仍然可以用于索引的快速完整扫描）。
In modern versions of Oracle, though, you can create an index on LOWER(class) which can then be used by this query.
但是，在现代版本的 Oracle 中，您可以在 LOWER(class) 上创建一个索引，然后该查询可以使用该索引。
