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

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 Received on Fri Jul 27 2001 - 14:18:29 CDT