Re: turn off/on case insensetive search in Oracle DBMS

In article <3B61BE85.D77078A6_at_attws.com>, "Daniel says...
>
>Thomas Kyte wrote:
>
>> In article <9jrs1g$16b$1_at_reader-00.news.insnet.cw.net>, "Andy says...
>> >
>> >Yes, you could do this - for most developers though this is totally
>> >impractical.
>> >
>> >It's just another thing that ORACLE doesn't cater for...Ohh, sorry, I mean
>> >doesn't lower itself to the level of all the other RDBMS.
>> >
>> >.
>>
>> since I can:
>>
>> create index on t(upper(field1));
>>
>> allowing for
>>
>> select * from t where upper(field1) = :x
>>
>> to use an index, why is this impractical?
>> http://osi.oracle.com/~tkyte/article1/index.html
>>
>>Additionally, there is interMedia Text which gives infinitely many more options
>>for searching text (stop words, near, and, or, fuzzy, soundex, stem, etc....).
>> It by default indexes in a case insensitive fashion. You can make it case
>> sensitive if you like.
>>
>> http://technet.oracle.com/doc/oracle8i_816/inter.816/a77063/toc.htm
>>
>> >
>> >FB
>> >
>> >"Thomas Liang" <liangluosheng_at_china.com> wrote in message
>> >news:9jr2nl$hmk$1_at_mail.cn99.com...
>> >> you can write
>> >> SELECT * FROM table WHERE UPPER(field1)='name'
>> >>
>> >> > Hello,
>> >> >
>> >> > we want to do some SQL-statement like 'SELECT FROM table WHERE field1 =
>> >> > "name"' not be affected by case sensitivity as in result we get "name"
>> >> > or "Name". Our actual installation of Oracle 8 works case sensitive.
>> >> >
>> >> > My DBA for our Oracle 8 DBMS told me, that it isn't possible to change
>> >> > this at the server site to "case insensitive".
>> >> >
>> >> > A developer told me, he had read that the server site is configurable
>> >> > for "case insensitive" or "case sensitive" (what we have at the moment).
>> >> >
>> >> > My question: Who is right?
>> >> >
>> >> > Thanks for help
>> >> > Bernd.
>> >> > --
>> >> > Bernd F. Dollinger
>> >> > eMail: Bernd.Dollinger_at_dv-werk.de
>> >>
>> >>
>> >
>> >
>>
>> --
>> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
>> Expert one on one Oracle, programming techniques and solutions for Oracle.
>> http://www.amazon.com/exec/obidos/ASIN/1861004826/
>> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
>I read this question very differently than you did. Turning off a search I
>interpreted as being a "switch" that could be flicked back and forth at will. A
>function based index will solve the problem. But you'll need to drop and rebuild
>the index to reverse it.
>
>Daniel A. Morgan
>

Actually I believe the poster was referring to (based on the tone of

>> >It's just another thing that ORACLE doesn't cater for...Ohh, sorry, I mean
>> >doesn't lower itself to the level of all the other RDBMS.

) the feature in SQL Server whereby a database can be built with case insensitive serching or not. For them, its all or nothing at the db level (its not a runtime switch one and off -- its an attribute of a database). For us, its a choice. In fact you can have your cake and eat it too:

create index t_idx on t(upper(x)); /* index for case insensitive searching */ create index t_idx2 on t(x); /* index for case insensitive searches */

So, if you need case insensitive searches,

1. use intermedia
2. use function based indexes

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp