Oracle 7.0: Case insensitive string comparisons?

This is a fairly standard problems but I do not know of an Oracle solution.

We have a database with a large number of names/addressess. We want to have the ability to do indexed lookups on these names/addresses but we alsow want to be able to generate nice looking mailings with the names capitalized correctly.

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.

  Store names/addresses in uppercase format, e.g MARK JONES   Searches will now work correctly but we are limited by the   Oracle INITCAP function when we generate mailings.    e.g. initcap("MARK JONES") -> Mark Jones

       but
         initcap("BASIL DEVERE") -> Basil Devere   (should be deVere)
       and
         initcap("FRED MCDONALD") -> Fred Mcdonald (should be McDonald)

  In Ingres there is a DBA solution to the problem. The DBA can define   character equivalency rules to be used for a specific database. This means   that in all comparisons "A" is equivalent to "a". Is there an Oracle   equivalence to this?

Would appreciate any help on this old and thorny problem?

        Thanks, MARK JONES

mcj_at_moose.cccs.umn.edu
University of Minnesota Received on Fri Jan 07 1994 - 17:58:00 CET