oracle查询区分大小写

转载地址：http://www.cnblogs.com/fangwenyu/archive/2010/04/14/1711969.html

ORACLE默认所存的值是取分大小写的，但有些需求想忽略大小写，今天就浅谈一下nls_sort ,nls_comp实现查询忽略大小写查询

view plain copy to clipboard print ?

1. anbob@ORCL> select * from testci ;   
2.   
3.         ID NAME                 REMARK   
4. ---------- -------------------- --------------------   
5.          1 a                    lower code   
6.          1 A                    upper code   
7.   
8. anbob@ORCL> select * from testci where name='a';   
9.   
10.         ID NAME                 REMARK   
11. ---------- -------------------- --------------------   
12.          1 a                    lower code   
13.   
14. anbob@ORCL> select * from  v$nls_parameters where parameter ='NLS_SORT';   
15.   
16. PARAMETER                         VALUE   
17. -------------------------------- ----------------   
18. NLS_SORT                          BINARY  
19.   
20. anbob@ORCL> ALTER SESSION SET NLS_SORT='BINARY_CI';   
21.   
22. Session altered.   
23.   
24. anbob@ORCL> select * from testci where name='a';   
25.   
26.         ID NAME                 REMARK   
27. ---------- -------------------- --------------------   
28.          1 a                    lower code   
29.   
30. anbob@ORCL> select * from  v$nls_parameters where parameter ='NLS_COMP';   
31.   
32. PARAMETER                         VALUE   
33. ---------------------------------- ------------------   
34. NLS_COMP                          BINARY  
35.   
36. anbob@ORCL> ALTER SESSION SET NLS_COMP=LINGUISTIC;   
37.   
38. Session altered.   
39.   
40. anbob@ORCL> select * from testci where name='a';   
41.   
42.         ID NAME                 REMARK   
43. ---------- -------------------- --------------------   
44.          1 a                    lower code   
45.          1 A                    upper code  

anbob@ORCL> select * from testci ;

        ID NAME                 REMARK
---------- -------------------- --------------------
         1 a                    lower code
         1 A                    upper code

anbob@ORCL> select * from testci where name='a';

        ID NAME                 REMARK
---------- -------------------- --------------------
         1 a                    lower code

anbob@ORCL> select * from  v$nls_parameters where parameter ='NLS_SORT';

PARAMETER                         VALUE
-------------------------------- ----------------
NLS_SORT                          BINARY

anbob@ORCL> ALTER SESSION SET NLS_SORT='BINARY_CI';

Session altered.

anbob@ORCL> select * from testci where name='a';

        ID NAME                 REMARK
---------- -------------------- --------------------
         1 a                    lower code

anbob@ORCL> select * from  v$nls_parameters where parameter ='NLS_COMP';

PARAMETER                         VALUE
---------------------------------- ------------------
NLS_COMP                          BINARY

anbob@ORCL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

anbob@ORCL> select * from testci where name='a';

        ID NAME                 REMARK
---------- -------------------- --------------------
         1 a                    lower code
         1 A                    upper code

NLS_COMP:
确定数据库中值的比较，10G中增加了LINGUISTIC，根据nls_sort参数指定进行比较，用来取代ansi,但ANSI为了向后兼容保留，还有一个值是BINARY根据字符的二进制值比较也是默认值，总共三个值

note:To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.

nls_sort:
指定order by 的查询顺序，If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of characters.

note: If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.

值非常多，可以通过V$NLS_VALID_VALUES where parameter=’SORT’ 查询

不过修改任何参数都要考虑性能问题

Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is set to BINARY. If NLS_SORT is set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.
因篇幅原因 就说这些，其实与这两个参数相关的还有很多…