Re: Case-insensitive search - HOW?

Mike Kohut, NorthwesTel wrote at 21 Jun 1995 02:11:22 GMT:
>
>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.
>
>Note: If your database is large, and you often do case-insensitive
>queries, then you should have a duplicate single-case field that
>you can search on. For example, you might have a mixed-case
>surname of 'McDonald', then have a secondary field containing
>'mcdonald'. By searching only on the single-case secondary field,
>you would speed up your queries considerably.
>

It is a simple trick to speed-up case-insensitive search.

SELECT * FROM EMP_TABLE WHERE (SURNAME LIKE 'S%' or SURNAME LIKE 's%') AND UPPER(SURNAME) = 'SMITH' Such query can utilize index on surname (query without this trick can't).

Alexandr Alesinsky
JSV Investor Received on Wed Jun 28 1995 - 00:00:00 CEST