Case insensitive queries

Folks:

Can anyone help us out with the following?

We are in process of evaluating Oracle and other databases. One of our requirements is for the database to store a mixture of upper, lower, and mixed case data, while allowing us to quickly and efficiently perform case insensitive queries against the data.

We would like to order the queries as if the data was of one case. Upper, lower, and mixed cases would have the same sort value and would compare as equal. For example, the following would appear after a query on all names which begin with "dev".

Devan
DeVito
deVoe
Devun

Note that the uppercase "V" is ordered the same as the lowercase
"v", and the uppercase "D" is ordered the same as the lowercase
"d".

DB2 and Sybase handle this situation internally. Oracle's response is to create a second column that holds an uppercase version of last name, query against the second indexed field, but use the original field to display the result to the end user.

Oracle's method requires us to duplicate every column that contains mixed case -- which in our shop is a lot of columns, resulting in a large database and possibly poor performance

Does anyone else have this same mixed case requirement? If so, how do you provide case insensitive support in Oracle?

As I have limited access to this newsgroup, please email any comments to:

Frank H. Walton
fwalton_at_akc.org
919-233-3610 Received on Wed Mar 20 1996 - 00:00:00 CET