Tracy,
IMHO the simplest and most efficient solution is :
1) to define a name_cleanup() function which does something like
replace(translate(replace(upper(arg), 'MC', 'MAC'), '- ', '##'),
'#', '')
(this is of course a very simple example)
2) to maintain by trigger an indexed CLEANED_UP_NAME which is just
name_cleanup(last_name) (you can do the same for first_name)
3) and to have your queries being written as
CLEANED_UP_NAME like name_cleanup(input) || '%'
Somebody has mentioned soundex, I am no great fan of soundex :
SQL> select soundex('mac gregor'), soundex('mcgregor')
2 from dual;
SOUN SOUN
---- ----
M226 M262
SQL> select soundex('thompson'), soundex('thomson')
2 from dual;
SOUN SOUN
---- ----
T512 T525
HTH,
Stephane Faroult
Tracy Rahmlow wrote:
>
> I am looking for an efficient solution to the following:
>
> We intend to capture information about a client such as:
>
> first name - John
> last name - McDonald
> phone numer - 222.222.2222
> zip code - 43333
> state - FL
> client number - 123343
>
> The names will be stored in mixed case for proper printing on client documents.
> The reps would like the flexiblity to enter the search criteria in a number of
> formats such as:
>
> 1) last name like mcdon* (wildcard) and first name = john
> 2) client number = 123343 (note: some clients do not always have their client
> number handy so it can not be the only available search mechanism)
> 3) last name = mac gregor (and locate both macgregor and mac gregor)
> 4) last name = kinney-jones (and locate both kinney-jones and kinney jones)
>
> How many indexes and of what type are required? Does the leading the column of
> an index have to be specified for the index to be used? I thought I remember
> hearing that that was a limitation of an older release, but that is no longer
> the case with 8 and up. Are there any white papers available that address the
> topic of client search and best practices?
>
> Thanks for your help!!
>
> American Express made the following
> annotations on 10/30/2003 04:11:07 PM
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
