Finding similar names in multiple tables

Question

I have multiple tables with different customer names. I am trying to find out how many times the same name is in a table. The challenge here is that someone could have entered the name as "John Smith" or "Smith, John".

There are 40,000 rows in each table and over 40 different tables. I am trying to query somehow without knowing the names but still return like names.

Basically I need to group like names without using a statement like:

WHERE cust_name LIKE '%john%'

How can you query multiple table columns using the contents of other table columns when the data within may not be in the same format? How would you best 'clean' the data to remove commas, spaces, etc?

Answer 1

WEll you have fuzzy logic avaliable in SSIS. I've used fuzzy grouing successfully to find duplicates -although you will want to match on more than name as many many people share the same names. I've done the match using name, address, phone and email. Fuzzy grouping allows you to use multiple fields for matching.

Answer 2

This really isn't a database problem. The real problem is coming up with an algorithm that will take a name and convert it into a standard format. This is hard to do and really depends on what your source data looks like. I would look through your source data and try to come up with some patterns to look for then use regular string manipulation to change them all into the same format.