Re: Case insensitive searching.

Fuzzy wrote:
>
> gwn_at_cyber3.servtech.com (g wayne nichols) wrote:
> >In <01bbb1c8$77ff0d80$bf0000c5_at_Garry.triniti.troitsk.ru> "Garry M. Filimonov" <garry_at_fly.triniti.troitsk.ru> writes:
> >>Fuzzy <grant_at_towersoft.com.au> wrote in article <32546d8c.0_at_red.interact.net.au>...
> >>>
> >>> Has anyone out there tried to implement case insensitive searching
> >>> using a nls module to fool the db?
> >>>
> >So far, we have solved this by storing the data in the database twice:
> >in mixed case (for display) and in upper case (for searching).
> >The upper case version is included in an index.
>
> We thought of this, but the data redundancy in a big db causes people
> to ask questions that we can only answer with "Because that's the way
> Oracle works".

To cut down on data redundancy, rather than duplicating all the data, just duplicate the first 2 or 3 characters (in upper case, indexed). Then:
  Select blah from emp
  where short_name = 'SMI'
  and upper(surname) = 'SMITH' ;

If Oracle plans its queries well, it will use the index on short_name to cut down the possible matches from all names in the db (millions) to just those few hundred starting SMI, quite quickly since it is using an index. Then it is another quick matter to pick out the SMITHs even without an index because you are down to just a few hundred rows. *If* Oracle plans its queries well. Try it and see...

James Received on Mon Oct 14 1996 - 00:00:00 CEST