Another Case Insensitive Question

From: Lynch, Julie K <jklynch_at_sandia.gov>
Date: Fri, 20 Sep 2002 09:58:49 -0800
Message-ID: <F001.004D54A0.20020920095849@fatcity.com>

Hello Everyone,

Access 2000 on Windows NT
Oracle 8.1.7.4.0 on HP-UX 11.0 64-bit
Currently both the front end and back end are in Access 2000 and we are moving the back end to Oracle.

I've got a situation here I'm not sure how to deal with. Does anyone know how to do case insensitive queries using an Access front end and Oracle back end? We can't change the Access front end due to the sheer number of select statements in queries/reports, so I need to somehow get the database to handle this.

We've got an application that does queries such as:

select * from <table> where name like 'smith'; in one query in Access.

and

select * from <table> where name like 'Smith'; in another query in Access.

and

select * from <table> where name like 'SMITH'; in some other report or query.

And the data in <table>.name could be SMITH,smith,Smith,sMith,smITH, so on and so forth.

Is there anyway to get the database to return all records where the name field contains smith,Smith,SMITH, etc. no matter which of the above queries is used??

Can't user InterMedia/Oracle Text since we can't change the queries on the front end.

Function based indexes???

Some how make ODBC convert mixed case like statements to upper or lower case?

TIA, Julie

Julie Lynch
Sandia National Laboratories
Oracle 8i DBA - OCP8i
Solaris 2.6,7/HP-UX 11.0 System Administrator Web Server Administrator

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Lynch, Julie K
INET: jklynch_at_sandia.gov

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Sep 20 2002 - 12:58:49 CDT