Re: Upper Case retrieval of Data - Possible ?

From: Nigel Thomas <nigel_at_preferisco.com>
Date: Tue, 27 Jun 2006 08:02:22 -0700 (PDT)
Message-ID: <20060627150223.97848.qmail@web54705.mail.yahoo.com>

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: Miller
MILLER
Millér"

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 27 2006 - 10:02:22 CDT