From the Globalization Guide for 9.2:
"Performing a Generic Base Letter Search
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:
I tried it:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL>
SQL> ALTER SESSION SET NLS_COMP=ANSI;
Session altered.
SQL>
SQL> ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;
Session altered.
SQL>
SQL> select distinct surname from names where surname='smith';
SURNAME
-----------------------------------
SMITH
Smith
SQL>
Watch out though; your execution plan may be lousy (I got index fast full scan).
Regards Nigel
