Re: CASE: Oracle 7 vs. 8?

In article <35e226a4.2474548_at_newshost.us.oracle.com>,   jkallman_at_us.oracle.com (Joel R. Kallman) wrote:
> On Fri, 21 Aug 1998 17:22:52 -0400, Patrick Durusau
> <pdurusau_at_unix.cc.emory.edu> wrote:
> >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?

Full-table-scan problems, I'm sure.

For a database with names, we have an indexed column that we use just for searching--it is a concatenation of "last,first middle", and is stored in all upper-case. We also have separate columns for Last, First, and Middle name/initial, and these are non-indexed, and are in mixed-case.

Your situation has no relation to the level of the Oracle database. It is just the way computer systems handle upper and lower-case characters. You will need to do something like we have in order for everything to work well. Write a trigger to maintain your all upper-case indexed column.

Steve Cosner

> 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.

If you do this, you lose the mixed-case capability of your names.

> 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.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Aug 24 1998 - 11:16:37 CDT