Re: Help! Case Insensitivity with SELECT..WHERE..LIKE queries

On Thu, 04 Sep 1997 16:02:59 +0300, Oleg <oleg_at_stat.ee> wrote:

>
>
>Eric B. Vest wrote:
>
>> Help, anybody:
>>
>> I'm trying to run a SELECT...WHERE...LIKE query on product names and
>> descriptions. However, I don't know of a way to ensure that the search
>> is case-insensitive, as the LIKE conditions are dependent on user
>> input. I need to make sure that the program returns a positive search
>> result when a user types in "malaysia" even if "Malaysia" is the actual
>> entry in the database.
>>
>> I'd humbly appreciate any assistance. Thanks.
>>
>> -- Nathan
>> ..................................................
>> Vest Technologies
>> http://www.vest.com
>> 350 Bay Street
>> Suite 100-123
>> San Francisco, CA 94133
>> 415/986-VEST
>
> That's to easy: use Upper lower clause.
>Oleg
>

Assuming that carrying the name or description data in a consistant state, like upper case only, is not desireable, then the use of a function in the WHERE clause, such as UPPER(), can ensure consistency between the data in the database and the user input as suggested. E.g.

SELECT ....
    FROM table_name a
 WHERE UPPER( a.product_name) LIKE UPPER(:user_input_item)

But this will negate the use of any index on "product_name". If the tables are small enough or response time is not overly critical then this may be the viable apprpoach.

Another tactic might be the creation of "case insensitive mirror columns" in the tables. So columns like product names or descriptions have duplicate columns that contain copies of the data translated to UPPER case, for example. The mirror columns can be maintained by database triggers, not by the application code, and can be indexed to optimize the efficiency of case insensitive style queries. E.g.

SELECT ....
    FROM table_name a
 WHERE a.product_name_mirror LIKE UPPER(:user_input_item)

Of course the downside is the increased data storage requirements which can be significant because we're doubling the storage of descriptive items which are usually rather lengthy.

If other strategies have been used, it would be nice to hear of them.

Bob Cunningham
bcunn_at_oanet.com
bcunn_at_compuserve.com Received on Tue Sep 09 1997 - 00:00:00 CDT