Re: Case insensitivity in Oracle 7.3 ???

The idea that I came across is the storage of both the upper case and normal data entry.
To select, one can use the case sensitive or the case insensitive field. Use of triggers can keep both fields the same ( pre-update trigger). And when looking for data use the apppropriate field. Obviously you can create indexes on either or both of the fields.

This storage idea works for one or two fields and obviously is impractical for larger number of fields.

Mike Krolewski

KeyStroke (Jack L. Swayze Sr.) wrote:

> In your SQL statements use the UPPER( function [or LOWER( if you prefer]
> every time you compare CHAR or VARCHAR values.
>
> For example, instead of:
>
> Select * from Emp where Emp_name = 'Anderson';
>
> Do this instead:
>
> Select * from Emp where upper(Emp_name) = upper('Anderson');
>
> However, I have been told that doing this will frequently cause the
> optimizer to ignore an index.
>
> David Pattinson wrote:
>
> > I'd appreciate some advice on how one could go about
> > 'faking' case-insensitive columns in a case-sensitive Oracle
> > 7.3 database. We are going to migrate our database from a
> > case_insensitive SQL Anywhere platform, and we want to store
> > the actual case the user typed in, but have case
> > insensitivity for purposes of searching/matching.
> > I can think of one solution involving adding forced upper
> > case columns for indexing and searching of text fields, but
> > am hoping that there is a better way...
> >
> > Regards, David.
Received on Thu Dec 17 1998 - 02:37:58 CST