| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: CASE: Oracle 7 vs. 8?
On Fri, 21 Aug 1998 17:22:52 -0400, Patrick Durusau
<pdurusau_at_unix.cc.emory.edu> wrote:
>Hello,
>
>My department is using an Oracle 7.3 database server with a database that
>will be going live after the first of the year. A question has arisen on
>doing case insensitive searching of records for last names, etc. We can
>use the UPPER() function to allow that type of searching but that
>introduces other problems.
What kind of problems?
If you normalize your data, convert it all to a common case, you can apply the UPPER or LOWER function to your *criteria* and not the column.
E.g., if the criteria is "Joel", you could do
SELECT fname FROM emp WHERE fname = UPPER('Joel')
If, instead, you did
SELECT fname FROM emp WHERE UPPER(fname) = UPPER('Joel')
you will first be applying the UPPER function to *all* the values of
the fname column.....full-table scan, which can most times be a bad
thing.
>
>I tried searching the Oracle website for an answer to how this is treated
>in Oracle 8 but had no success. Is this a feature(bug) in Oracle 8 or does
>it allow case insensitive searching without the problems of UPPER()?
>
I guarantee you...this is *not* a bug. There are many facilities to do this properly, and this does conform to the SQL standard.
>Many thanks,
>
>Patrick
>
>Patrick Durusau
>Information Technology
>Scholars Press
>pdurusau_at_emory.edu
>
>
Thanks!
Joel
Joel R. Kallman Oracle Government, Education, & Health
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com
 |
 |