Oracle Database 9i / 10g Case Insensitive Searching

How to store data as case insinsetive in oracle database for either 9i or 10gR2 ?



Case Insensitive searching / comparison of data can be made possible by setting
the NLS session level parameter NLS_COMP to ANSI in 9i and LINGUISTIC in 10g.



Along with this you also have to set for 9i NLS_SORT=GENERIC_BASELETTER
and for 10g NLS_SORT=BINARY_CI

Example :

For a 9i database

You can perform a search that ignores case and diacritics.

Enter the following statements:

ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;

Then enter a statement similar to the following:
SELECT * FROM emp WHERE ename='miller';

This statement can now return names that include the following:
Miller
MILLER
Millér

References

http://download.oracle.com/docs/cd/B10501_01/server.920/a96529/ch4.htm#1005868