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

From: Daniel A. Morgan <Daniel.Morgan_at_attws.com>
Date: Mon, 30 Jul 2001 12:47:59 -0700
Message-ID: <3B65B9EF.3F6FD41C@attws.com>

Thomas Kyte wrote:

> 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

Thanks.

Daniel A. Morgan Received on Mon Jul 30 2001 - 14:47:59 CDT