Does anyone know of any DB wide option to allow ORACLE to do case
insensitive compares?
- We want to store information as mixed case (names, etc)
We've looked at the following options so far:
- SELECT * FROM EMP WHERE UPPER('smith') = UPPER(EMP_NAME) does not use
an index and thus does a full table scan each time.
- SELECT * FROM EMP WHERE
( UPPER('smith') = UPPER(EMP_NAME) AND
(EMP_NAME LIKE 'Sm%' OR EMP_NAME LIKE 'sM%' OR
EMP_NAME LIKE 'SM%' OR EMP_NAME LIKE 'sm%');
does force the optimizer to use the index, but is tedious to code, you
can't expect a end-user to use in an ad-hoc environment, and I'm
concerned about future changes to the optimizer invaliding the concept.
- Storing a separate upper-case version of each field used to search.
EMP_NAME_UPPER would be populated by a trigger that would uppercase the
value of EMP_NAME. EMP_NAME_UPPER would be indexed and used for
searches, while EMP_NAME would contain the mixed case value. This option
uses extra space and you have to educate developers and users how to do
searches.
Thanks.
Received on Wed May 21 1997 - 00:00:00 CDT
