Oracle 中不区分大小写的搜索

Question

LIKE 和其他比较运算符 = 等的默认行为区分大小写。

是否可以使它们不区分大小写？

Answer 1

也许你可以尝试使用

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

Answer 2

从 Oracle 12c R2 开始，您可以使用 整理运算符：

COLLATE 运算符确定表达式的排序规则。此运算符使您能够覆盖数据库使用标准排序规则派生规则为表达式派生的排序规则。

COLLATE 运算符采用一个参数 collat​​ion_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 演示

Answer 3

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

WHERE name LIKE 'j%' COLLATE BINARY_CI 

而不是这个：

WHERE name COLLATE BINARY_CI LIKE 'j%'

无论如何，我喜欢 COLLATE 运算符解决方案，原因如下：

• 你只在表达式中放置一次，并且不需要担心多个 UPPER 或 LOWER 以及将它们放在哪里
• 它与您需要的确切语句和表达式隔离，这与 ALTER SESSION 解决方案不同，ALTER SESSION 解决方案使其适用于所有内容。无论数据库或会话 NLS_SORT 设置如何，您的查询都将一致地工作。

Answer 4

select user_name
from my_table
where nlssort(user_name, 'NLS_SORT = Latin_CI') = nlssort('%AbC%', 'NLS_SORT = Latin_CI')

Answer 5

你可以这样做：

where regexp_like(name, 'string
, 'i');

Answer 6

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

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

1. 将列和字符串的大小写保持一致。

您可以使用 强制所有数据大小写相同UPPER() 或 LOWER()：

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

或者

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

如果 column_1 未在 upper(column_1) 或 lower(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 演示了所有这些查询中发生的情况。请注意解释计划，它指示何时使用索引以及何时不使用索引。

2.使用正则表达式。

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

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

select * from my_table where regexp_like(column_1, '^my_string
为了执行 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_COMP 确定的 NLS_SORT 值，

  使用的排序规则是 BINARY。

 NLS_COMP的默认值为BINARY；但是，LINGUISTIC指定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'));

, 'i');

为了执行 LIKE 的等效功能，可以删除这些。

请小心这一点，因为您的字符串可能包含正则表达式引擎会以不同方式解释的字符。

此 SQL Fiddle 显示相同的示例输出，但使用 REGEXP_LIKE() 除外。

3. 在会话级别更改它。

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

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

您还需要更改 NLS_COMP 参数;引用：

遵守 NLS_SORT 参数的确切运算符和查询子句
取决于 NLS_COMP 参数的值。如果操作员或
子句不遵循由 NLS_COMP 确定的 NLS_SORT 值，
使用的排序规则是 BINARY。

NLS_COMP的默认值为BINARY；但是，LINGUISTIC指定Oracle应该注意NLS_SORT的值：

WHERE 子句和 PL/SQL 中所有 SQL 操作的比较
块应使用 NLS_SORT 中指定的语言排序
范围。为了提高性能，您还可以定义一个
您想要其语言的列的语言索引
比较。

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

如文档中所述，您可能想要创建一个 语言索引以提高性能

Answer 7

从 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 但它似乎也适用于旧的 = 。

---

在早于 10gR2 的版本中，这实际上是不可能完成的，如果您不需要不区分重音的搜索，通常的方法就是只UPPER()列和搜索表达式。