Oracle中处理不区分大小写查询的处理 .

来源：互联网发布：海量数据股票目标价编辑：程序博客网时间：2024/05/21 15:07

今天做jsp的一个查询，因中间用到对yhid的查询，此字段为字母或数字。所以大小写都不尽相同，如：LGR，LgR,lgR，当查询时不管输入大小写，都应当把这些记录查询出来。

select *
from (SELECT

               t.yhid,//这里的yhid还不变，用于列表的显示，显示的结果还是大小写都有
               t.bmbh,
               t.bmmc,
               t.xm,
               t.yhmm,
               t.sfzh,
               t.jyhm,
               t.sjhm,
               lower(t.yhid) lower_yhid //在最后一列加了重新对yhid列出一次，用于后面的查询条件，
          FROM USER_INFO T) a
WHERE 1 = 1
   and lower_yhid like '%lgr%'//这里的'%lgr%'在java中进行了小写处理 yhid.tolower()

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

白天学习了一下 SQL Server 的大小写处理的情况。SQL Server 中大小写区分的处理。

晚上学习学习 Oracle 的大小写处理的情况。

还真是彻底不一样啊

查询数据的时候。

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

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

下面就是 Oracle 的明细部分了。

首先是测试表/数据

SQL> CREATE TABLE tab (

2 id INT,

3 val VARCHAR2(10)

4 );

表已创建。

SQL> INSERT INTO tab

2 SELECT 1, 'ABC123' FROM DUAL UNION ALL

3 SELECT 2, 'abc234' FROM DUALUNION ALL

4 SELECT 3, 'Abc345' FROM DUAL UNION ALL

5 SELECT 4, 'aBC456' FROM DUALUNION ALL

6 SELECT 5, 'aBc567' FROM DUAL;

已创建5行。

=操作的例子

SQL> SELECT

2 *

3 FROM

4 tab

5 WHERE

6 val = 'abc123';

未选定行

SQL> SELECT

2 *

3 FROM

4 tab

5 WHERE

6 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

2 *

3 FROM

4 tab

5 WHERE

6 val = 'abc123';

ID VAL

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

1 ABC123

LIKE操作的例子

SQL> SELECT

2 *

3 FROM

4 tab

5 WHERE

6 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

2 *

3 FROM

4 tab

5 WHERE

6 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

2 *

3 FROM

4 tab

5 WHERE

6 REGEXP_LIKE (val, '^a');

ID VAL

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

2 abc234

4 aBC456

5 aBc567

ALTER SESSION SET … 之后

SQL> SELECT

2 *

3 FROM

4 tab

5 WHERE

6 REGEXP_LIKE (val, '^a');

ID VAL

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

1 ABC123

2 abc234

3 Abc345

4 aBC456

5 aBc567

排序的例子

SQL> SELECT

2 *

3 FROM

4 Tab

5 ORDER BY

6 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

2 *

3 FROM

4 Tab

5 ORDER BY

6 val;

ID VAL

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

1 ABC123

2 abc234

3 Abc345

4 aBC456

5 aBc567

关于中文汉字的排序

SQL> TRUNCATE TABLE Tab;

表被截断。

SQL>

SQL> INSERT INTO tab

2 SELECT 1, '一' FROM DUAL UNION ALL

3 SELECT 2, '二' FROM DUAL UNION ALL

4 SELECT 3, '三' FROM DUAL UNION ALL

5 SELECT 4, '四' FROM DUAL UNION ALL

6 SELECT 5, '五' FROM DUAL;

已创建5行。

SQL> commit;

提交完成。

-- 按照拼音来排序

SQL> SELECT

2 *

3 FROM

4 Tab

5 ORDER BY NLSSORT(val, 'NLS_SORT=SCHINESE_PINYIN_M');

ID VAL

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

2 二

3 三

4 四

5 五

1 一

-- 按照笔画数来排序

SQL> SELECT

2 *

3 FROM

4 Tab

5 ORDER BY NLSSORT(val, 'NLS_SORT=SCHINESE_STROKE_M');

ID VAL

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

1 一

2 二

3 三

5 五

4 四

-- 按照偏旁部首来排序

SQL> INSERT INTO tab

2 SELECT 6, '人' FROM DUAL UNION ALL

3 SELECT 7, '十' FROM DUAL UNION ALL

4 SELECT 8, '土' FROM DUAL UNION ALL

5 SELECT 9, '士' FROM DUAL;

已创建4行。

SQL> commit;

提交完成。

SQL> SELECT

2 *

3 FROM

4 Tab

5 ORDER BY NLSSORT(val, 'NLS_SORT=SCHINESE_RADICAL_M');

ID VAL

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

1 一

2 二

3 三

5 五

6 人

7 十

4 四

8 土

9 士

已选择9行。