Re: case INsensitive queries

Steve Larson wrote:
>
> Does anyone know of a way to store data in mixed case in an Oracle database,
> yet have Oracle ignore case when parsing where statements?
>
> Is it possible to acheive this by defining my own character set? Any hints on
> how to do this?

This is probably the most FA of the FAQ's!!

Short answer: Oracle doesn't supply any _direct_ way to do this.

Medium answer: Many folks will tell you to do something like:

SELECT foo, bar, baz -- baz is the column with the mixed-case data FROM mytable
WHERE UPPER(baz) = 'FRIBBLE';

This works, **BUT** if there is an index on baz, using UPPER(baz) guarantees that it will NOT ever be used (functions in the WHERE clause disable indexes on any column used as an argument in the function)

Long answer: if your application allows, create an **additional** column, say "ubaz" in which the contents of baz are "translated" **during insert/update**
(i.e. INSERT INTO mytable

      (foo, bar, baz, ubaz)
      VALUES
      ('foovalue', 'barvalue', 'BaZvAlUe', UPPER('BaZvAlUe'))
      .....

and then put the index on ubaz, not baz
(or in addition to the one on baz)

Then, doing

SELECT foo, bar, baz -- baz is the column with the mixed-case data FROM mytable
WHERE ubaz = 'FRIBBLE'; -- note use of ubaz not UPPER(baz)!!

will use the index on ubaz and perform as expected. Received on Thu Oct 09 1997 - 00:00:00 CDT