Re: Case-insensitive search - HOW?

In article <3s7v4b$9q8_at_spot.YKnet.yk.ca>, "Mike Kohut, NorthwesTel" <mkohutnw_at_yknet.yk.ca> writes:

>al-her_at_sectra.se (Almut Herzog) wrote:
>> I'm an Informix user in need to implement a case-insensitive search.
 This
>> is quite ugly in Informix. How would you do it with Oracle?
>>
>Use the UPPER function in your Oracle query.
>
>For example: SELECT * FROM EMP_TABLE WHERE UPPER(SURNAME) = 'SMITH'
>
>This will match surnames such as 'SMITH', 'smith', 'Smith', etc.
>
>

This will certainly work, but will prevent Oracle from using an index on SURNAME because of the function. One of the tricks that is used in Forms 4.x to allow case insensitive queries is as follows: (using 'Smith' as the example again)

SELECT * FROM EMP_TABLE
WHERE UPPER(SURNAME) = UPPER('Smith')

  and (   SURNAME LIKE 'SM%'
       or SURNAME LIKE 'sm%'
       or SURNAME LIKE 'Sm%'
       or SURNAME LIKE 'sM%')

Essentially the first step finds exact matches but cannot use an index. Then the four conditions combined with 'OR' will match any of the potential case different entries for the name SMITH that we are looking for. This came from one of the presentations by Steve Meunch (Forms Product Manager, Oracle Corp) at last year's International Oracle User Group meeting. The Forms product team apparently tested this algorithm and found that two characters were enough to provide an effecient query (one character i.e. 'S%' OR 's%' is not detailed enough, and three characters gets too complicated to figure out all of the permutations).

Anyway, hope that this is what you were looking for!

Regards,
Dave Rathbun
Integra Solutions
Dallas, TX
DRathbun_at_AOL.com Received on Fri Jun 23 1995 - 00:00:00 CEST