Re: how to store data in mixed case in database

FYI, one of the common problems with storing Mixed Case text is that indexing it is (almost) impossible. Many people often create triggers to populate a second column that is an UPPER version of the text being stored. You can then index the UPPER version, and do your queries using the index on the UPPER column.

A waste of space, but a workaround for a feature that Oracle didn't get around to until 8i.

Peter

In article <7tgan6$bqd$1_at_nnrp1.deja.com>,   markp7832_at_my-deja.com wrote:
> In article <7tfuck$1sb$1_at_nnrp1.deja.com>,
> malikg_at_my-deja.com wrote:
> > I am looking for a way to store data in oracle database in mixed
case.
> > Instead of converting data on insertion to upper or lower case i
need
> a
> > way to find to do this automatically when the data is being inserted
> so
> > it does not matter how its being retrieved. With the UPPER AND
LOWER
> > function I can only do this when the data has been inserted and then
> > convert it into UPPER case in order to retrieve it in mixed case. is
> > there an init.ora parameter or some kind of session set up command
> that
> > can achieve that functionality. For instance in sql server the Case
> can
> > be set up before hand. does oracle has any such functionality.
> > thanks for any help in advance
> >
> By default Oracle stores character data in the format it is submitted
> in so if your store mixed case you retrieve mixed case. You can
define
> check constraints to force upper or lower case be used for a specific
> column. You can also use before insert/update triggers to edit input.
>
> update nick_names
> set nick_name = 'Gossip Queen'
> where empno = 10105;
>
> The nick_name column will contain mixed case unless there is a trigger
> that edits it to upper or lower case.
>
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 06 1999 - 16:35:28 CDT