Re: How to turn off case sensitive searches

Brian Peasland wrote in message <37DE58FB.437222DE_at_edcmail.cr.usgs.gov>...
>BJones8888 wrote:
>>
>> This should be an easy one for someone: How do I turn off the Oracle
default
>> of case sensitive searches?
>>
>> Bob Jones
>> Omni Developments, Inc. --- (Custom Software Development)

>You can't. The best you can do is use the UPPER or LOWER functions in
>your search.
>For example:
>
> SELECT * FROM TABLE WHERE UPPER(searchstring) = UPPER(column); (or
>LOWER)
>
>This will convert the column values and the search string to upper (or
>lower) case. Unfortunately, this will also suppress your indexes and
>make your query potentially run longer; unless you are using Oracle 8i
>where you can make a function based index.
>
>HTH,
>Brian
>

One option that may or may not suit your purposes is to add an extra column to the table. This should be a duplicate of the column being searched on (with a different name, naturally). Then create a trigger on the table to populate this second column with an upper-case copy of the first column. Searches can then be performed on the duplicate column.

e.g.
EMP table:
(

    SURNAME varchar2(30),
    SURNAME_UPPER varchar2(30)
)

Trigger populates SURNAME_UPPER based on contents of SURNAME.

We used this method as a simple way to pre-process data, looking for possible duplicates.

Regards,
Liam McCauley Received on Wed Sep 15 1999 - 09:25:44 CDT