Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: CASE: Oracle 7 vs. 8?

Re: CASE: Oracle 7 vs. 8?

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: Sun, 23 Aug 1998 14:49:27 GMT
Message-ID: <35e226a4.2474548@newshost.us.oracle.com>


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




The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Sun Aug 23 1998 - 09:49:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US