Re: Case Insensitive Searches

From: Peter Asboeck <Peter.Asboeck_at_servodata.co.at>
Date: 1997/10/30
Message-ID: <3458E321.1E716C41@servodata.co.at>#1/1

Joe White wrote:

> I am looking for a way to do case insensitive searches, such as on
> names(MacNeill vs Macneill). I dont want to lose my indexing. Any
> suggestions.
>
> jw

There is only a trick.

Add a column with uppercase/lowercase entry the search criteria. (This can be done on RDBMS level (v7) - e.g. using a before insert/update
trigger on row level).
Generate an index for the uppercase/lowercase search criteria.

e.g.

table emp
  emp_id
  emp_name
  ...
  s_emp_name

                                       |     indexed column
                                       v
  1   Smith                        SMITH
  2   MacNeill                   MACNEILL
  3   Macneill                    MACNEILL

Redirect the queries to s_emp_name

  select *
    from emp
   where s_emp_name = upper(:your_search_name);

This is the typical storage (additional column, additional index) versus time
trick.

Hope this helps.

Peter Received on Thu Oct 30 1997 - 00:00:00 CST