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.
相关文章