Oracle中不区分大小写的搜索

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

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

353305 次浏览
小开

也许你可以试试

SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE '%ME%'
小开
最佳答案

从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以上的版本中，这实际上无法实现，如果不需要accent-insensitive搜索，通常的方法是将列和搜索表达式都UPPER()。

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

在不使用全文索引的情况下，在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小提琴演示了在所有这些查询中发生的事情。请注意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小提琴显示了相同的示例输出，只是使用了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中指定的语言排序 参数。为了提高性能，还可以定义 要用于其的列上的语言索引 比较。< / p >

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

alter session set nls_comp=LINGUISTIC

如文档中所述，你可能想要创建语言索引来提高性能

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

你可以这样做:

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

在Oracle 12c R2中，可以使用COLLATE operator:

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

COLLATE操作符接受一个参数collation_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<>小提琴demo

小开
如果你把COLLATE操作符放在表达式的末尾，它也可以工作，在我看来这更干净。 所以你可以使用这个:

WHERE name LIKE 'j%' COLLATE BINARY_CI

而不是这样:

WHERE name COLLATE BINARY_CI LIKE 'j%'

无论如何，我喜欢COLLATE操作符解决方案的原因如下:

  • 你只在表达式中放入一次，你不需要担心多个UPPER或LOWER，以及把它们放在哪里
  • 它被隔离到您需要它的确切语句和表达式中，不像ALTER SESSION解决方案使它适用于所有事情。无论数据库或会话的NLS_SORT设置如何，您的查询都将始终工作。

提交答案