Re: Upper Case retrieval of Data - Possible ?
- From: Nigel Thomas <nigel@xxxxxxxxxxxxxx>
- To: salem.ghassan@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 27 Jun 2006 08:02:22 -0700 (PDT)
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
Other related posts:
