Re: Oracle queries that is case in-sensitive

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Thu, 15 Apr 1999 09:08:52 -0700
Message-ID: <37160F14.E694277F@us.oracle.com>

By default, Oracle stores data in the case it is entered by the users. Case-insensitive queries have a number of ways of being done, depending on the version you're running:

In 8i, you have the capability to build function-based indexes. You could use this to create an index on the upper or lower value of the column, then the index can be used in queries that say where upper(column_name) = 'SMITH' for example.
In earlier releases, you could work around this by using functions on the left side of the where clause (where upper(column_name) = 'SMITH', for example). The downside was this negated the use of any index on column_name.
An alternative is to add an additional column to the table, put a trigger on the table that places the "upper'ed" value in the additional column, index that additional column, then query on the additional column. Downside is additional storage requirements and application changes.

HTH. Pete

cdoganay_at_my-dejanews.com wrote:

> Hi, is there any way or parameter (Without modifying applications) that will
> make Oracle Database , case in-sensitive, which will enable the queries to
> give the same results whether search is initiated bu lower case or upper
> case.
>
> Thanks for your help,
>
> Cemal Doganay
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Regards

Pete

Peter Sharman                             Email: psharman_at_us.oracle.com
WISE Course Development Manager           Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education            (650)607 0109 (local)

San Francisco

SQL> select standard_disclaimer, witty_remark 2 from company_requirements;

Opinions are mine and do not necessarily reflect those of Oracle Corporation

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

Received on Thu Apr 15 1999 - 11:08:52 CDT