sql - Oracle 中不区分大小写的搜索

Question

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

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

Answer 1

在不使用全文索引的情况下，在 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) 上建立索引，这可能会强制进行全表扫描.为了避免这种情况，您可以创建一个 function-based index .

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

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

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

This SQL Fiddle演示了所有这些查询中发生的情况。请注意解释计划，它指示何时使用索引以及何时不使用索引。

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

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

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

3。在 session 级别更改它。

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

alter session set nls_sort=BINARY_CI

关于 linguistic sorting and string searching 有很多附加信息如果您想指定不同的语言，或使用 BINARY_AI 进行不区分重音的搜索。

您还需要更改 NLS_COMP范围;引用:

The exact operators and query clauses that obey the NLS_SORT parameter depend on the value of the NLS_COMP parameter. If an operator or clause does not obey the NLS_SORT value, as determined by NLS_COMP, the collation used is BINARY.

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

Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.

因此，您需要再次更改 session

alter session set nls_comp=LINGUISTIC

如文档中所述，您可能希望创建一个 linguistic index提高性能

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