Re: Case insensitive searching.

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>...
>>> People,
>>>
>>> Has anyone out there tried to implement case insensitive searching
>>> using a nls module to fool the db? When I spoke to Oracle about this,
>>> they (honestly) suggested that the best way to it was to use SQL like
>>>
 

>>Why don't:
 

>>Select blah from blah where UPPER(name) like 'SM%';

>Because this disables any index on "name", so it could be a long while
>before you see your results.

Exactly! Most of the databases that will be used have the relevant table holding millions of rows, which means hours or even days to get a result. Seems like you've been down this path before, Wayne.

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

>Of course, in Clipper I would just have defined an index on UPPER(name)
>and be done with it. :-)

Mmmm. SQLBase has this ability as well. Does anyone fancy my chances of getting Oracle to implement this? I think old Satan will be skating to work that day :-) :-) :-)

Ciao
Fuzzy
:-) Received on Wed Oct 09 1996 - 00:00:00 CEST