SQL Oracle 中不区分大小写的搜索

Case insensitive searching in Oracle

提问by sergionni

The default behaviour of LIKEand the other comparison operators, =etc is case-sensitive.

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

Is it possible make them case-insensitive?

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

采纳答案by álvaro González

Since 10gR2, Oracle allows to fine-tune the behaviour of string comparisons by setting the NLS_COMPand NLS_SORTsession parameters:

从 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

You can also create case insensitive indexes:

您还可以创建不区分大小写的索引：

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

This information was taken from Oracle case insensitive searches. The article mentions REGEXP_LIKEbut it seems to work with good old =as well.

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

---

---

In versions older than 10gR2 it can't really be done and the usual approach, if you don't need accent-insensitivesearch, is to just UPPER()both the column and the search expression.

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

回答by Ben

There are 3 main ways to perform a case-insensitive search in Oracle without using full-text indexes.

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

Ultimately what method you choose is dependent on your individual circumstances; the main thing to remember is that to improve performance you must index correctly for case-insensitive searching.

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

1. Case your column and your string identically.

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

You can force all your data to be the same case by using UPPER()or LOWER():

您可以使用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');

If column_1is not indexed on upper(column_1)or lower(column_1), as appropriate, this may force a full table scan. In order to avoid this you can create a function-based index.

如果column_1未在upper(column_1)或 上建立索引lower(column_1)，则可能会强制执行全表扫描。为了避免这种情况，您可以创建一个基于函数的索引。

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

If you're using LIKE then you have to concatenate a %around the string you're searching for.

如果您使用 LIKE，那么您必须将 a 连接到%您正在搜索的字符串周围。

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

This SQL Fiddledemonstrates what happens in all these queries. Note the Explain Plans, which indicate when an index is being used and when it isn't.

这个 SQL Fiddle演示了所有这些查询中会发生什么。请注意解释计划，它指示何时使用索引，何时不使用。

2. Use regular expressions.

2. 使用正则表达式。

From Oracle 10g onwards REGEXP_LIKE()is available. You can specify the _match_parameter_ 'i', in order to perform case-insensitive searching.

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

In order to use this as an equality operator you must specify the start and end of the string, which is denoted by the carat and the dollar sign.

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

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

In order to perform the equivalent of LIKE, these can be removed.

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

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

Be careful with this as your string may contain characters that will be interpreted differently by the regular expression engine.

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

This SQL Fiddleshows you the same example output except using REGEXP_LIKE().

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

3. Change it at the session level.

3. 在会话级别更改它。

The NLS_SORTparameter governs the collation sequence for ordering and the various comparison operators, including =and LIKE. You can specify a binary, case-insensitive, sort by altering the session. This will mean that every query performed in that session will perform case-insensitive parameters.

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

alter session set nls_sort=BINARY_CI

There's plenty of additional information around linguistic sorting and string searchingif you want to specify a different language, or do an accent-insensitive search using BINARY_AI.

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

You will also need to change the NLS_COMPparameter; to quote:

您还需要更改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_SORT 参数的确切运算符和查询子句取决于 NLS_COMP 参数的值。如果运算符或子句不遵守 NLS_SORT 值（由 NLS_COMP 确定），则使用的归类为 BINARY。

The default value of NLS_COMP is BINARY; but, LINGUISTIC specifies that Oracle should pay attention to the value of NLS_SORT:

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.

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

So, once again, you need to alter the session

所以，再一次，你需要改变会话

alter session set nls_comp=LINGUISTIC

As noted in the documentation you may want to create a linguistic indexto improve performance

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

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

回答by V4Vendetta

maybe you can try using

也许你可以尝试使用

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

回答by Lukasz Szozda

From Oracle 12c R2 you could use COLLATE operator:

从 Oracle 12c R2 开始，您可以使用COLLATE operator：

The COLLATE operator determines the collation for an expression. This operator enables you to override the collation that the database would have derived for the expression using standard collation derivation rules.

The COLLATE operator takes one argument, collation_name, for which you can specify a named collation or pseudo-collation. If the collation name contains a space, then you must enclose the name in double quotation marks.

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

COLLATE 运算符采用一个参数 collat​​ion_name，您可以为其指定命名排序规则或伪排序规则。如果归类名称包含空格，则必须将名称括在双引号中。

Demo:

演示：

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 demo

db<>小提琴演示

回答by Clodoaldo Neto

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

回答by grep

you can do something like that:

你可以这样做：

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