如果将COLLATE操作符放在表达式的末尾，它也可以工作，这对我来说似乎更干净。 所以你可以用这个:

WHERE name LIKE 'j%' COLLATE BINARY_CI 

而不是这样:

WHERE name COLLATE BINARY_CI LIKE 'j%'

无论如何，我喜欢COLLATE操作符解决方案的原因如下:

你只在表达式中放入一次，你不需要担心多个UPPER或LOWER，以及把它们放在哪里 它被隔离到您需要它的确切语句和表达式中，不像ALTER SESSION解决方案使它适用于所有事情。无论数据库或会话的NLS_SORT设置如何，您的查询都将始终工作。

也许你可以试试

SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE '%ME%'

从10gR2开始，Oracle允许通过设置NLS_COMP和NLS_SORT会话参数来微调字符串比较的行为:

SQL> SET HEADING OFF
SQL> SELECT *
  2  FROM NLS_SESSION_PARAMETERS
  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT
BINARY

NLS_COMP
BINARY


SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
  2  FROM DUAL;

         0

SQL>
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

SQL>
SQL> SELECT *
  2  FROM NLS_SESSION_PARAMETERS
  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT
BINARY_CI

NLS_COMP
LINGUISTIC


SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
  2  FROM DUAL;

         1

你也可以创建不区分大小写的索引:

create index
   nlsci1_gen_person
on
   MY_PERSON
   (NLSSORT
      (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
   )
;

此信息来自Oracle不区分大小写的搜索。文章提到了REGEXP_LIKE，但它似乎也适用于old =。

---

在10gR2以上的版本中，这实际上是做不到的，如果不需要不区分重音的搜索，通常的方法是对列和搜索表达式都使用UPPER()。

在Oracle 12c R2中使用COLLATE操作符:

COLLATE操作符确定表达式的排序规则。此操作符使您能够覆盖数据库使用标准排序规则派生的表达式的排序规则。 COLLATE操作符接受一个参数collation_name，您可以为其指定命名排序规则或伪排序规则。如果排序规则名称包含空格，则必须将名称括在双引号中。

演示:

CREATE TABLE tab1(i INT PRIMARY KEY, name VARCHAR2(100));

INSERT INTO tab1(i, name) VALUES (1, 'John');
INSERT INTO tab1(i, name) VALUES (2, 'Joe');
INSERT INTO tab1(i, name) VALUES (3, 'Billy'); 
--========================================================================--
SELECT /*csv*/ *
FROM tab1
WHERE name = 'jOHN' ;
-- no rows selected

SELECT /*csv*/ *
FROM tab1
WHERE name COLLATE BINARY_CI = 'jOHN' ;
/*
"I","NAME"
1,"John"
*/

SELECT /*csv*/ *
FROM tab1 
WHERE name LIKE 'j%';
-- no rows selected

SELECT /*csv*/ *
FROM tab1 
WHERE name COLLATE BINARY_CI LIKE 'j%';
/*
"I","NAME"
1,"John"
2,"Joe"
*/

db < > fiddle演示

在不使用全文索引的情况下，在Oracle中执行不区分大小写的搜索主要有3种方法。

最终，你选择哪种方法取决于你的个人情况;要记住的主要事情是，为了提高性能，您必须为不区分大小写的搜索正确地建立索引。

1. 列和字符串的大小写相同。

你可以使用UPPER()或LOWER()强制所有数据都是相同的情况:

select * from my_table where upper(column_1) = upper('my_string');

or

select * from my_table where lower(column_1) = lower('my_string');

如果column_1没有在上(column_1)或下(column_1)建立索引(视情况而定)，这可能会强制执行全表扫描。为了避免这种情况，可以创建基于函数的索引。

create index my_index on my_table ( lower(column_1) );

如果你使用LIKE，那么你必须在你正在搜索的字符串周围连接一个%。

select * from my_table where lower(column_1) LIKE lower('my_string') || '%';

这个SQL Fiddle演示了在所有这些查询中发生了什么。请注意Explain Plans，它指示何时使用索引，何时不使用索引。

2. 使用正则表达式。

从Oracle 10g开始，REGEXP_LIKE()可用。您可以指定_match_parameter_ 'i'，以便执行不区分大小写的搜索。

为了将其用作相等运算符，必须指定字符串的开始和结束，字符串由克拉和美元符号表示。

select * from my_table where regexp_like(column_1, '^my_string$', 'i');

为了执行类似LIKE的操作，可以删除这些。

select * from my_table where regexp_like(column_1, 'my_string', 'i');

要注意这一点，因为您的字符串可能包含正则表达式引擎解释不同的字符。

这个SQL Fiddle向您展示了相同的示例输出，只是使用了REGEXP_LIKE()。

3.在会话级别更改它。

NLS_SORT参数管理排序的排序顺序和各种比较操作符，包括=和LIKE。您可以通过更改会话来指定二进制的、不区分大小写的排序。这意味着在该会话中执行的每个查询都将执行不区分大小写的参数。

alter session set nls_sort=BINARY_CI

如果您想指定一种不同的语言，或者使用BINARY_AI进行不区分重音的搜索，还有大量关于语言排序和字符串搜索的额外信息。

你还需要改变NLS_COMP参数;引用:

符合NLS_SORT参数的精确操作符和查询子句 取决于NLS_COMP参数的值。如果操作符或 子句不服从NLS_SORT值，由NLS_COMP决定， 使用的排序规则是BINARY。

NLS_COMP的默认值为BINARY;但是，language指定Oracle应该注意NLS_SORT的值:

比较WHERE子句和PL/SQL中的所有SQL操作 块应该使用NLS_SORT中指定的语言排序 参数。为了提高性能，还可以定义 要用于其的列上的语言索引 比较。

因此，您需要再次更改会话

alter session set nls_comp=LINGUISTIC

如文档中所述，您可能希望创建语言索引来提高性能

create index my_linguistc_index on my_table 
   (NLSSORT(column_1, 'NLS_SORT = BINARY_CI'));

如果将COLLATE操作符放在表达式的末尾，它也可以工作，这对我来说似乎更干净。 所以你可以用这个:

WHERE name LIKE 'j%' COLLATE BINARY_CI 

而不是这样:

WHERE name COLLATE BINARY_CI LIKE 'j%'

无论如何，我喜欢COLLATE操作符解决方案的原因如下:

你只在表达式中放入一次，你不需要担心多个UPPER或LOWER，以及把它们放在哪里 它被隔离到您需要它的确切语句和表达式中，不像ALTER SESSION解决方案使它适用于所有事情。无论数据库或会话的NLS_SORT设置如何，您的查询都将始终工作。