On 20/05/13 18:53, lcampbell wrote:

> Here's one I hope others have encountered (and possibly found an elegant
> solution).
> ... SNIP ...
> constituent's last name (in uppercase). It works well for individuals,
> but fails to catch my 'Corlis Estates' example above. Yes, I could break
> the incoming business name string into individual words, eliminate the
> common "The, A, An, The, Inc,..." and submit individual words in a chain
> of "or"s. Has anybody got a better way?
>
> Lane C.
> NW Software

I would be thinking of a SOUNDEX function for this. I need one myself to
ensure that the "fat fingered" user - me - of my Music collection can
type in a name that's roughly spelt correctly.

I did something similar years ago on Oracle where there is a Soundex
function built in. The code offered an exact lookup - it selected
directly from a table using a WHERE clause, or a fuzzy search - it used
a soundex table which was linked to the main table via Primary key.

All words in the search field(s) were soundexed - they have to be. Using
my music collection as an example:

Elton John would have two entries in the soundex table, one for Elton,
one for John. Anyone looking for John something or something John, would
lookup the "john, Jon, Johanne etc" soundex code (4 characters starting
with J) and offer a list of all the Artists in the artist table, whose
PK is listed as containing the word John or similar to it.

So:

ARTIST TABLE:

ID bigint PK
NAME ....
...


SOUNDEX TABLE:

ARTIST_ID bigint
SOUNDEX CHAR(4)


EXAMPLE ARTIST:

1 Elton John
2 Runrig
...


EXAMPLE SOUNDEX:

1 E435
1 J500
2 R562
...


The Soundex for Jon, John, Johanne etc all boil down to J500, so I would
go in to the soundex table looking for the J500 and from that, pull out
the artist PK id (1) and joing on that to the Atrist table to get my
match/matches.

Hope this makes sense.


Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767