Re: Searching by Name

It would be an assumption, but from your example, the databases being
searched are already broken into different columns for first, middle, last or some
variation?
It helps if some other piece of info, like State Code or Zip Code or Phone Nbr
can be part of the select. City is hard because of the variations of spelling,
plus Margate is a suburb of Ft Lauderdale, & either can fit the address.

Having only one large database to search, I came up with something like this:
1. search exact match first - if no matches
2. search partial first & partial last - usually no more than 2 on first and 5 for
last name,
so Joeseph and Joe both match Jo, and Smith and Smith Jr match Smith.

A table scan is too expensive on large files so a trigger on the databases to
maintain a common file of the shortened fields

Customer State Key
JOE SMITH NC 12345
JOSEPH SMITTY MD 123456
JOE THOMPSON NC 12398

Prospect
JOE SAMPSON NM 202556
JAMES SMATHERS AZ 222507
JOHN SMOTHERS RI 156889

Combined
JO SAMPS NM Prospect 202556
JA SMATH AZ Prospect 222507
JO SMITH NC Customer 12345
JO SMITT MD Customer 123456
JO SMOTH RI Prospect 156889
JO THOMP NC Customer 12398

We let users enter full name, but internally took apart if no exact match.
Remove all punctuation (even hyphens) and standardize the upper/lower
either to all upper, or enforced mixed. Foreign names are difficult,
especially the ordering of 3 or more name "words" where the name
given a telephone operator to look up may be in different order than
database.

This was used for a 7-10 million names database.


----- Original Message ----- From: "Holm, Paul" <pholm@xxxxxxxxxxxxxxxxx>
To: <web400@xxxxxxxxxxxx>
Sent: Wednesday, February 19, 2014 6:01 PM
Subject: [WEB400] Searching by Name

Looking for insight or recommendations or experiences with the following:

A client has SQL based web application that searches multiple databases on
different systems on many fields but in particular: NAME

Issue: User is looking for a person named "Joe", they type Joe into the
search and don't get the full results because databases are storing the
name as "Joseph". Same thing with Nikki and Nicole.

Obviously, we can use SQL wildcards % and LIKE so you could search by "Jo%"
or "Ni%" but users may not do that. I've started to analyze FULL TEXT
searches. Anyone already been down this road?

Thanks, Paul Holm
--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing list
To post a message email: WEB400@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/web400.