Edit Question - KnowledgeHub

Title Body

I have a need to match cold leads against a database of our clients. The leads come from a third party provider in bulk (thousands of records) and sales is asking us to (in their words) "filter out our clients" so they don't try to sell our service to a established client. Obviously, there are misspellings in the leads. Charles becomes Charlie, Joseph becomes Joe, etc. So I can't really just do a filter comparing lead_first_name to client_first_name, etc. I need to use some sort of string similarity mechanism. Right now I'm using the lovely difflib to compare the leads' first and last names to a list generated with Client.objects.all() . It works, but because of the number of clients it tends to be slow. I know that most sql databases have soundex and difference functions. See my test of it in the update below - it doesn't work as well as difflib. Is there another solution? Is there a better solution? Edit: Soundex, at least in my db, doesn't behave as well as difflib. Here is a simple test - look for "Joe Lopes" in a table containing "Joseph Lopes": with temp (first_name, last_name) as ( select 'Joseph', 'Lopes' union select 'Joe', 'Satriani' union select 'CZ', 'Lopes' union select 'Blah', 'Lopes' union select 'Antonio', 'Lopes' union select 'Carlos', 'Lopes' ) select first_name, last_name from temp where difference(first_name+' '+last_name, 'Joe Lopes') >= 3 order by difference(first_name+' '+last_name, 'Joe Lopes') The above returns "Joe Satriani" as the only match. Even reducing the similarity threshold to 2 doesn't return "Joseph Lopes" as a potential match. But difflib does a much better job: difflib.get_close_matc

Tags (comma-separated) Cancel