Re: Case insensitive searching.

In article <32546d8c.0_at_red.interact.net.au> grant_at_towersoft.com.au (Fuzzy) writes:
>From: grant_at_towersoft.com.au (Fuzzy)
>Subject: Case insensitive searching.
>Date: Fri, 04 Oct 1996 00:54:45 GMT
 

>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
 

> Select blah from blah where
> ( name like 'SM%' or
> name like 'sm%' or
> name like 'Sm%' or
> name like 'sM%') and
> to_upper(name) = 'SMITH';
 

>Foolishly, I was kind of hoping for a response from my server sometime
>this century, rather than go the way of the pharohs using the above
>method.
 

>Has anyone tried any other methods of achieving this. (I have a TAR
>currently open requesting an upgrade that supports case-insensitive
>searching, but I think I'm the 50000000th person to ask - and be
>declined!)
 

>Ciao
>Fuzzy
>:-)

What we are doing is this:

• We add a column (say upper_name)
• The column is filled with a before insert or update trigger :new.upper_name := upper(:new.name)
• The column upper_name is indexed
• The search is made on upper_name

It works fine.

Michel Lalonde. Received on Mon Oct 07 1996 - 00:00:00 CEST