Search Postgresql Archives

Re: "Fuzzy" Matches on Nicknames

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello Michael,
On Tue, 2016-11-29 at 19:10 -0500, Michael Sheaver wrote:
> Greetings,
> 
> I have two tables that are populated using large datasets from
> disparate external systems, and I am trying to match records by
> customer name between these two tables. I do not have any
> authoritative key, such as customerID or nationalID, by which I can
> match them up, and I have found many cases where the same customer
> has different first names in the two datasets. A sampling of the
> differences is as follows:
> 
> Michael <=> Mike
> Tom <=> Thomas
> Liz <=> Elizabeth
> Margaret <=> Maggie
> 
> How can I build a query in PostgreSQL (v. 9.6) that will find
> possible matches like these on nicknames? My initial guess is that I
> would have to either find or build some sort of intermediary table
> that contains associated names like those above. Sometimes though,
> there will be more than matching pairs, like:
> 
> Jim <=> James <=> Jimmy <=> Jimmie
> Bill <=> Will <=> Willie <=> William
> 
> and so forth.
> 
> Has anyone used or developed PostgreSQL queries that will find
> matches like these? I am running all my database queries. on my local
> laptops (Win7 and macOS), so performance or uptime is no issue here.
> I am curious to see how others in this community have creatively
> solved this common problem.
> 
> One of the PostgreSQL dictionaries (synonym, thesaurus etc.) might
> work here, but honestly I am clueless as to how to set this up or use
> it in queries successfully.
> 
> Thanks,
> Michael (aka Mike, aka Mikey)
> 

Check out chapter F15 in the doco.
Try the double metaphone.
I worked on something similar many years ago cleaning up input created
by data entry clerks from hand written speeding tickets, so as to match
with "trusted" data held in a database.
As the volume of input was small in comparison with the number of
licensed drivers, we could iterate over and over again trying to match
it up.

HTH.
Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux