Re: Case insensitive queries

Dear Frank,
at the moment I don't have the time to test through your problem, but I'll try a quick answer out of my experiences:

It should work if you use the function UPPER() or LOWER()

SELECT UPPER( <column name> )
FROM <table>
WHERE UPPER( <column name> ) LIKE 'HUGO%' ORDER BY UPPER( <column name> ) ;

This should return all rows with 'HUGO' leading in the column, and ordered by this column, in both cases regardless to the case.

Special case:
If you want to change certain characters only you can use the function TRANSLATE: SELECT TRANSLATE ( <column name> , 'abcdef', 'ABCDEF') ..

then only the six characters a, b, c, d, e and f get changed to uppercase-letters (or whatever you choose) and the rest of the whole character-set remains unchanged.

I hope this helps,
sincerely
H. WILD

+==========================================================================+
| Hartlieb  WILD        Systemanalyse    Programmierung     EDV-Training   |
|                     allgemein beeideter gerichtlicher Sachverständiger   |
|                                                                          |
| A-6073 SISTRANS 280   e-mail:  whsysana_at_ping.at     Tel.: (++43) (0)512  |
| Austria               CompuServe:   100131,1552                 378 110  |
+==========================================================================+

> 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 Sat Mar 30 1996 - 00:00:00 CET