为了账号安全,请及时绑定邮箱和手机立即绑定

在Oracle中进行不区分大小写的搜索

/ 猿问

在Oracle中进行不区分大小写的搜索

暮色呼如 2019-07-29 16:04:41

在Oracle中进行不区分大小写的搜索

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

是否有可能使它们不区分大小写?


查看完整描述

3 回答

?
慕斯709654

从10gR2开始,Oracle允许通过设置NLS_COMPNLS_SORT会话参数来微调字符串比较的行为:

SQL> SET HEADING OFFSQL> 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;

         0SQL>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_personon
   MY_PERSON   (NLSSORT      (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
   );

此信息来自Oracle不区分大小写的搜索。文章提到REGEXP_LIKE但它似乎=也很好用。


在10gR2以上的版本中,它无法真正完成,如果您不需要不区分重复性的搜索,通常的方法就是UPPER()列和搜索表达式。


查看完整回答
反对 回复 2019-07-29
?
慕沐林林

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

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

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小提琴演示了所有这些查询中发生的事情。请注意解释计划,它指示何时使用索引以及何时使用索引。

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

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

除了使用REGEXP_LIKE()之外,此SQL Fiddle显示相同的示例输出。

3.在会话级别更改它。

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

alter session set nls_sort=BINARY_CI

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

您还需要更改NLS_COMP参数; 报价:

遵循NLS_SORT参数的确切运算符和查询子句取决于NLS_COMP参数的值。如果运算符或子句不遵循由NLS_COMP确定的NLS_SORT值,则使用的排序规则是BINARY。

NLS_COMP的默认值是BINARY; 但是,LINGUISTIC指定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'));


查看完整回答
反对 回复 2019-07-29
?
明月笑刀无情

也许你可以尝试使用

SELECT user_nameFROM user_masterWHERE upper(user_name) LIKE '%ME%'


查看完整回答
反对 回复 2019-07-29

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信