Re: Case sensitive/insensitive?

In article <MARY.95Jun6153638_at_shark>, mary_at_mitra.com (Mary E. Hunt) wrote:

> Oracle appears to be case sensitive when executing a query on a
> char/varchar2 type of field. I would like the data to be entered what
> ever way the user wishes, but would like to be able to query the data
> without using the upper() function, as this won't use the index.
>
> I know MS-Access is case insensitive. Ie I enter a value into col1 as
> "Text" and I can select where col1 = 'text'.
>
> What is the standard for SQL databases?? Case sensitive on character
> fields or case insensitive?
>
> If you have any ideas, because I am going to have to figure out a work
> around for Oracle being case sensitive.

Oracle stores varchar2 data as it is entered. If the column is indexed, the value that is indexed is exactly as entered.

The only workaround that I know of is to have a mirrored field that contains the UPPER version of the varchar2 field. If an index is on the UPPER version, you can search it by putting the search criteria in uppercase and using the index.

How to maintain this field's linked relationship with its "as entered" sibling depends on your database version, your query tool, your data entry tool, and/or your reporting tool.

Hope this helps.

Matt... Received on Thu Jun 08 1995 - 00:00:00 CEST