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