Re: Case Insensitve Text Searching

On Mon, 19 May 1997 22:35:38 +0100, Bola Ogunlana <bolao_at_incads.demon.co.uk> wrote:

>In article <5li5ga$2sj_at_client2.news.psi.net>, Brad Pliner
><brad_at_iliad.com> writes
>>
>>What's the best way to do case insensitive text searching?
>>
>>Here's what I'm considering:
>>Suppose I'm searching the names of movies. I'm create a field for the name,
>>as it will be displayed to the user "The Long Kiss Goodnight". Additionally
>>I'll create a field for the name, all capitalized, and strip out all
>>instances of "the", "of", etc. ("LONG KISS GOODNIGHT"). I'll use that field
>>to search by.
>>
>>Any thoughts? Is this the best way to do this?
>>
>> - Brad
>>
>>_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
>>_
>> Bradley S. Pliner mailto:brad_at_pliner.com Internet Software
>> Pliner Products http://www.pliner.com/ Custom Development
>>- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>>-
>
>You could possible try using the "Upper" or "Lower" functions instead.
>eg if you want to search for "search_string" you'll do something like;
>
>select name from movies where upper(movie_name) like
>upper('%&search_string%');
>
>This way there's no need to create an additional column.
>Get the gist?

Unfortunately, using this method will mean table scans, as any indexes you have will be ignored by the optimiser.

The first idea of a second column to search by is equal to any other method that has been devised for Oracle.

Ciao
Fuzzy
:-)   Received on Wed May 21 1997 - 00:00:00 CDT