Oracle中不区分大小写的搜索

LIKE和其他比较操作符，= etc的默认行为区分大小写。

有没有可能让它们不区分大小写?

当前回答

在不使用全文索引的情况下，在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没有在上(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'));

2013-02-09 11:29:36

其他回答

也许你可以试试

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

2011-03-22 12:23:30

从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()。

2011-03-22 12:31:43

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

2011-03-22 12:32:15