Re: Query and UPPER function

David P wrote:
> > I then attempted the following query and used the UPPER
> >function.
> >
> >Select FIRST_NAME,LAST_NAME,MIDDLE_NAME
> >From patient
> >Where UPPER(LAST_NAME) LIKE 'ANDERSON%'
> >and UPPER(FIRST_NAME) LIKE 'HAROLD%'
> >Order By LAST_NAME;
> >
> >
> > This query took about 6 minutes to come back. It seems to be
> >going through all the rows and doing the comparison.
> >
> >
> > Since I can't make the assumption that the Last/First Name
> >is going to be all uppercase, does anyone know of a way to speed
> >up the second query?
>
> UPDATE MY_TABLE
> SET LAST_NAME = UPPER(LAST_NAME),
> SET FIRST_NAME=UPPER(FIRST_NAME);
>
> it'll take 6 minutes, but only once
> :)
> Regards
> Dave

If you do not want to store the names in upper case in the database, you could add 2 columns to the table (UPPER_LAST_NAME, UPPER_FIRST_NAME) which could be populated by database trigger. Then create a before insert/update trigger on the table for each row that has the body:

:new.upper_last_name := upper(:new.last_name); :new.upper_first_name := upper(:new.first_name);

Then perform your searches on the upper case column but display the mixed case columns.

Darin Brown Received on Tue Jul 09 1996 - 00:00:00 CEST