SQL Oracle DB：如何编写忽略大小写的查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1031844/
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
Oracle DB: How can I write query ignoring case?
提问by zeroDivisible
As I had written in title, I have SQL query, run on Oracle DB, lets say:
正如我在标题中所写的，我有 SQL 查询，在 Oracle DB 上运行，让我们说：
SELECT * FROM TABLE WHERE TABLE.NAME Like 'IgNoReCaSe'
If I would like, that the query would return either "IGNORECASE", "ignorecase" or combinations of them, how can this be done?
如果我愿意，查询将返回“IGNORECASE”、“ignorecase”或它们的组合，如何做到这一点？
Is this possible?
这可能吗？
采纳答案by devio
You can use ALTER SESSION statements to set comparison to case-insensitive. See this FAQ.
您可以使用 ALTER SESSION 语句将比较设置为不区分大小写。请参阅此常见问题解答。
alter session set NLS_COMP=ANSI;
alter session set NLS_SORT=BINARY_CI;
For all those visiting 8 years after this original answer has been accepted (for 10gR2):
对于所有在此原始答案被接受 8 年后访问的人（对于 10gR2）：
After 10gR2, the NLS_COMPsetting must be `LINGUISTIC':
在 10gR2 之后，NLS_COMP设置必须是“LINGUISTIC”：
ALTER SESSION SET NLS_COMP=LINGUISTIC;
回答by Hooloovoo
Select * from table where upper(table.name) like upper('IgNoreCaSe');
Alternatively, substitute lower for upper.
或者，将下层替换为上层。
回答by joe
You can use either lower or upper function on both sides of the where condition
您可以在 where 条件的两侧使用 lower 或 upper 函数
回答by kMAP
You could also use Regular Expressions:
您还可以使用正则表达式：
SELECT * FROM TABLE WHERE REGEXP_LIKE (TABLE.NAME,'IgNoReCaSe','i');
回答by akf
You can use the upper() function in your query, and to increase performance you can use a function-base index
您可以在查询中使用 upper() 函数，为了提高性能，您可以使用基于函数的索引
CREATE INDEX upper_index_name ON table(upper(name))
回答by ozczecho
...also do the conversion to upper or lower outside of the query:
...还可以在查询之外转换为上限或下限：
tableName:= UPPER(someValue || '%');
...
...
Select * from table where upper(table.name) like tableName
回答by user3666177
You can convert both values to upper or lowercase using the upperor lowerfunctions:
您可以使用upper或lower函数将两个值转换为大写或小写：
Select * from table where upper(table.name) like upper('IgNoreCaSe')
or
或者
Select * from table where lower(table.name) like lower('IgNoreCaSe');
回答by Gandalf
Also don't forget the obvious, does the data in the tables need to have case? You could only insert rows already in lower case (or convert the existing DB rows to lower case) and be done with it right from the start.
也不要忘记显而易见的，表中的数据需要有大小写吗？您只能插入已经小写的行（或将现有的 DB 行转换为小写）并从一开始就完成。
