Re: Oracle 7.0: Case insensitive string comparisons?

chrisj_at_pro-data.demon.co.uk (Chris Jack) writes:

:In article <7JAN199410585816_at_cccs.umn.edu> mcj_at_cccs.umn.edu writes:
 

:>
:>This is a fairly standard problems but I do not know of an Oracle solution.
:>
:>Potential solutions we have considered:
:> Store names/addresses in normal format, e.g Mark Jones
:> Problem indexed search will not find match if search criteria
:> entered as MARK JONES, and if we force the database column to
:> uppercase in the comparison then index will not be used.
:>
:Can't you make the index on _at_upper() of the column?

Not in a SQL-based product like Oracle. First of all, there is no _at_upper function, although there is an UPPER function. Second, I don't believe it is permitted to have index columns based on functions or computed values, they must be composed of actual columns in the table.

As the original posted mentiond, the suggestion of doing the query as "... where UPPER(column) = 'VALUE' ..." would work but would probably not be able to take advantage of any indexes to speed up searches.

About the only solution I can come up with would be to have another column which is the UPPER of the original column, maintained with database triggers, and indexed to improve performance. This is not particularly elegant and is somewhat wasteful for disk space, but then Oracle is a bit of a disk hog anyway.

---
Michael Nolan, Tailored Software Services, Inc.
Lincoln, Nebraska
Sysop for the GEnie DBMS RoundTable
nolan_at_notes.tssi.com, dbms_at_genie.geis.com