Re: case insensitive search

Hi,

One problem is that use of the upper/lower functions will disable the use of search based on an index (given there are an index based on your column).

A way to come over this is to write some insert (and/or update) trigger to keep a (new) column of the table updated with a uppercase (or lowercase) version of the column, and the have an index on this column. Then the trick is - of course - to make any search to be based on the new column.

How you "bring up the results" is fairly dependent on which tool you are using for presentation purposes. If it's Forms then you should be able to have the mixed-case column displayed, and then do some gymnastics in a key-exeqry trigger to populate the uppercase (or lowercase) non-displayed field of the form (remember to blank the mixed-case field afterwards).

Hope this helps.

Regards,

Michael Ringbo

Anurag Minocha wrote:

> Hi,
> Thanks for your help.
> In your case i will have to store the data in upper or lower case. How do i
> handle those cases where I store the data as it is.
>
> eg:- aNuRag
>
> when i bring up the results it should be as above but the search should be
> case insensitive
>
> anurag
>
> reply at
> anurag_at_synergy-infoech.com
>
> Suresh Bhat wrote:
>
> > Hi -
> >
> > In your WHERE clause you would simply use:
> >
> > where upper(column1) = 'ABCDEF...... etc.' or you can use lower function
> > also.
> >
> > This sounds too simple. Is this what you are looking for ?
> >
> > Suresh Bhat
> >
> > Anurag Minocha <anurag_at_synergy-infotech.com> wrote in article
> > <377B7F36.F48D6830_at_synergy-infotech.com>...
> > > Hi,
> > > how do i do CaseInsensitive search on varchar2 columns .
> > >
> > > anurag
> > >
> > > reply at
> > > anurag_at_synergy-infotech.com
> > >
> > >
Received on Fri Jul 02 1999 - 01:55:10 CDT