Re: Client Search Info Needed

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: sfaroult_at_oriole.com

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).