Oracle 中不区分大小写的搜索

Case insensitive searching in Oracle(Oracle 中不区分大小写的搜索)

问题描述

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

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

是否可以让它们不区分大小写?

Is it possible make them case-insensitive?

推荐答案

自 10gR2 起，Oracle 允许通过设置 NLS_COMP 和 NLS_SORT 会话参数:

Since 10gR2, Oracle allows to fine-tune the behaviour of string comparisons by setting the NLS_COMP and NLS_SORT session parameters:

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

此信息取自 Oracle 不区分大小写的搜索.文章提到了 REGEXP_LIKE，但它似乎也适用于旧的 =.

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

在 10gR2 之前的版本中，它实际上无法完成，如果您不需要 accent-insensitive 搜索，通常的方法就是 UPPER() 列和搜索表达式.

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

这篇关于Oracle 中不区分大小写的搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！