Re: Case Settings in Oracle

Pre Oracle 8i...No...you'd have to either use UPPER (or LOWER) functions in your queries such as...

SELECT *
  FROM <table>
 WHERE UPPER(<column_name>) = 'ABC';

...which would preclude the use of indexes

...OR do something like...

SELECT *

  FROM <table>
 WHERE <column_name> LIKE 'Ab%'
    OR <column_name> LIKE 'AB%'
    OR <column_name> LIKE 'aB%'
    OR <column_name> LIKE 'ab%'

...which is a trick that Oracle Forms uses for its mixed case queries to allow indexes to be used to a certain extent

...OR maintain an extra column on the table that always contained the UPPER (or LOWER) case value (i.e. a behind the scenes duplicate column maintained by triggers...not application code) that could be indexed and allow an efficient query such as...

SELECT *
  FROM <table>
 WHERE <upper_case_indexed_column> = 'ABC';

With Oracle 8i function based indexes are introduced that will alleviate this problem.

On Sun, 29 Aug 1999 21:28:29 +0100, "Craig Beevers" <cbeevers_at_easynet.co.uk> wrote:

>I forgot to mention that the Oracle resides on a SUN box (Solaris 2.6).
>
>When loaded on an NT Oracle install, the Case is not a problem.
>
>--
>Best Regards
>Craig Beevers
>
>Craig Beevers <cbeevers_at_easynet.co.uk> wrote in message
>news:7qbn2q$17s7$1_at_quince.news.easynet.net...
>>
>> Hi,
>>
>> Is there a way that you can change the whole of an Oracle instance (or
>> specific tables) to ignore case when searching
>> e.g Select * from x
>> where col = " abc"
>>
>> col
>> ------
>> ABC
>> abc
>> ABc
>>
>> On MS SQL Server 6.5 , you had to reinstall and change the Sort Order.
>>
>> Is there an equivalent in Oracle 7.3 or 8.0.5. ?
>>
>> I am aware of the Upper () and Lower (), I am looking for something more
>> global.
>>
>> --
>> Best Regards
>>
>> Craig Beevers
>> beevers_at_maincontrol.co.uk
>>
>>
>>
>>
>
>
Received on Mon Aug 30 1999 - 05:49:06 CEST