Oracle DB:如何编写查询忽略用例?

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?

这是可能的吗?

8 个解决方案

#1

You can use ALTER SESSION statements to set comparison to case-insensitive. See this FAQ.

可以使用ALTER SESSION语句将比较设置为不区分大小写。看到这个FAQ。

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_COMP setting must be `LINGUISTIC':

10gR2之后，NLS_COMP设置必须为“语言学”:

ALTER SESSION SET NLS_COMP=LINGUISTIC;

#2

103

Select * from table where upper(table.name) like upper('IgNoreCaSe');

Alternatively, substitute lower for upper.

或者，用低代替高。

#3

You can use either lower or upper function on both sides of the where condition

在where条件的两边都可以使用上下函数

#4

You could also use Regular Expressions:

你也可以使用正则表达式:

SELECT * FROM TABLE WHERE REGEXP_LIKE (TABLE.NAME,'IgNoReCaSe','i');

#5

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))

#6

...also do the conversion to upper or lower outside of the query:

…还可以在查询之外进行上下转换:

tableName:= UPPER(someValue || '%');

...

…

Select * from table where upper(table.name) like tableName

#7

You can convert both values to upper or lowercase using the upper or lower functions:

您可以使用上下函数将两个值转换为大写或小写:

Select * from table where upper(table.name) like upper('IgNoreCaSe') or lower(table.name) like lower('IgNoreCaSe');

#8

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.

同样，不要忘记一个显而易见的事实，表中的数据是否需要用case?您只能插入以小写形式存在的行(或者将现有的DB行转换为小写形式)，并从一开始就使用它。

#1