Oracle 实现查询不区分大小写(设置数据库)

刘泽宇

oracle 默认 区分大小写。如果不要区分，就要额外的设置。


首先是测试表/数据

SQL> CREATE TABLE tab ( id  INT,val VARCHAR2(10));

表已创建。

SQL> INSERT INTO tab  values (1, 'ABC123'),(2, 'abc234'),(3, 'Abc345'),(4, 'aBC456'),(5, 'aBc567')

已创建5行。

=操作的例子

SQL> SELECT * FROM   tab WHERE  val = 'abc123';
无查询结果

SQL> SELECT   *  FROM  tab WHERE   val = 'ABC123';
查询结果

       ID VAL

---------- ----------

        1 ABC123


由上面的结果可以看出，默认情况下，Oracle 大小写敏感的。

下面通过设置环境变量，让Oracle对大小写不敏感

SQL> ALTER SESSION SET NLS_COMP=ANSI;

会话已更改。

SQL> ALTER SESSION SET NLS_SORT=binary_ci;

会话已更改。

SQL> SELECT  *  FROM tab WHERE val = 'abc123';


       ID VAL

---------- ----------

        1 ABC123



LIKE操作的例子

SQL> SELECT  * FROM tab  WHERE val LIKE 'a%';
       ID VAL

---------- ----------

        2 abc234

        4 aBC456

        5 aBc567



默认是大小写敏感。


SQL> ALTER SESSION SET NLS_COMP=ANSI;

会话已更改。

SQL> ALTER SESSION SET NLS_SORT=binary_ci;

会话已更改。


会话更改以后，条件是  val = 的，可以实现大小写不敏感

但是对于 val LIKE 的查询，还是大小写敏感的。

解决办法，使用正则表达式的方式来变通的处理。

SQL> SELECT  * FROM   tab  WHERE  REGEXP_LIKE (val, '^a', 'i');
       ID VAL

---------- ----------

        1 ABC123

        2 abc234

        3 Abc345

        4 aBC456

        5 aBc567



注1：REGEXP_LIKE 的第3个参数'i' 表示大小写敏感。

注2：对于环境变量NLS_COMP 与NLS_SORT的设置，会影响REGEXP_LIKE 执行的结果（假如第3个参数不填写的话）。


默认情况下：

SQL> SELECT  *  FROM   tab  WHERE  REGEXP_LIKE (val, '^a');
       ID VAL

---------- ----------

        2 abc234

        4 aBC456

        5 aBc567



ALTER SESSION SET … 之后

SQL> SELECT   *  FROM    tab WHERE  REGEXP_LIKE (val, '^a');
      ID VAL

---------- ----------

        1 ABC123

        2 abc234

        3 Abc345

        4 aBC456

        5 aBc567


排序的例子

SQL> SELECT  * FROM   Tab ORDER BY val;
       ID VAL

---------- ---------

        1 ABC123

        3 Abc345

        4 aBC456

        5 aBc567

        2 abc234

默认排序为大小写敏感

SQL> ALTER SESSION SET NLS_COMP=ANSI;

会话已更改。

SQL> ALTER SESSION SET NLS_SORT=binary_ci;

会话已更改。

SQL> SELECT  * FROM  Tab ORDER BY  val;
       ID VAL

---------- ----------

        1 ABC123

        2 abc234

        3 Abc345

        4 aBC456

        5 aBc567


关于中文汉字的排序

SQL> TRUNCATE TABLE Tab;
表被截断。

复制代码
SQL> INSERT INTO tab   
SELECT 1, '一' FROM DUAL UNION ALL   
SELECT 2, '二' FROM DUAL UNION ALL
SELECT 3, '三' FROM DUAL UNION ALL
SELECT 4, '四' FROM DUAL UNION ALL
SELECT 5, '五' FROM DUAL;


已创建5行。

SQL> commit;

提交完成。


-- 按照拼音来排序

SQL> SELECT * FROM Tab ORDER BY NLSSORT(val, 'NLS_SORT=SCHINESE_PINYIN_M');


       ID VAL

---------- ----------

        2 二

        3 三

        4 四

        5 五

        1 一



-- 按照笔画数来排序

SQL> SELECT * FROM Tab ORDER BY NLSSORT(val, 'NLS_SORT=SCHINESE_STROKE_M');



       ID VAL

---------- ----------

        1 一

        2 二

        3 三

        5 五

        4 四



-- 按照偏旁部首来排序



SQL> INSERT INTO tab   
SELECT 6, '人' FROM DUAL UNION ALL
SELECT 7, '十' FROM DUAL UNION ALL
SELECT 8, '土' FROM DUAL UNION ALL
SELECT 9, '士' FROM DUAL;

已创建4行。

SQL> commit;

提交完成。


SQL> SELECT * FROM   Tab ORDER BY NLSSORT(val, 'NLS_SORT=SCHINESE_RADICAL_M');


       ID VAL

---------- ----------

        1 一

        2 二

        3 三

        5 五

        6 人

        7 十

        4 四

        8 土

        9 士



已选择9行。