Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

So I have some table with Name, Address, and Zip with no record data attached; and I have a table which has all the same, but has more information and I need a way to merge the tables when they don't match 100%.

How do I match them up if they aren't Identical? I'm a newb @ SQL, but I know they won't match up for the most part and I can't be the only one with this issue. However software which will do this has proven to be difficult.

Writing software to do this would even be worse than having to do it in the first place.

I know I can do this in excel; kinda, but with the amount of records I have its proving to be difficult over a million.

share|improve this question

closed as off topic by Nifle, Dave Rook, 8088, Kyle Jones, CharlieRB Jan 4 '13 at 20:29

Questions on Super User are expected to relate to computer software or computer hardware within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Do you just want to count the records that have the same name, address, etc, or a subset of those values? –  soandos Sep 27 '12 at 3:22
    
What fields can you match on? SQL's fuzzy-matching isn't great. –  user3463 Sep 27 '12 at 3:23
    
Yes I want to basically add phone number to a buntch of records I got from our client. He gave me a list to sale, but didn't actually include the phone number for over 1/2 the records. I do have a 3 million phone numbers in the state. I figure the accuracy automatically goes up with Zip Code, Normalize the Addresses (Not 100% sure how to do) and then Fuzzy match on First Name & Last name. Logically I know how to do it. I would think someone else must have had the same issue –  Crazyd Sep 27 '12 at 15:25

1 Answer 1

I used to work at a database marketing firm (sorry for sending you junk mail). It was our job to figure out if "Robert Jones 671 Kimbrough SPFD MO 65802" is the same as "Bobbie Joanes 671 Kimbrough St. Sprinfield MO 65809" If we didn't make a match, we risked sending duplicate mail to a potential customer which would make our clients look dumb as well as waste their money.

Our approach was to decompose the problem down into smaller domains and apply different criteria to answer is A probably B. Too rigid of a matching rule and you won't catch duplicates. Too loose of a matching rule and you'll throw away potential customers.

We had three domains an entity could match on: Name, Method of contact, Relationship. A match was only allowed if we matched across two of the domains.

Method of contact

A method of contact was mail, email or phone.

Addresses

The first step is to standardize an address provided. The end goal is to take your input address and have it corrected to the USPS standard. In the preceding example, both addresses would probably get mail delivered to them but only because the postal carrier understood the intention of the sender. The real address would be

671 S KIMBROUGH AVE SPRINGFIELD MO 65806-3342

Once you have a consistent address, then address matching are a much easier problem to solve. You still need to worry about addresses that are not correctable as well as what the rules are for multi-tenant locations (Suite 200, Apt B, etc) but that's part of the fine tuning you'd need to work out with the business owner. Oh, and even though the +4 digits are handy for delivery, don't let those factor into your address matching logic. Those are far more likely to change than the 5 digit postal code.

Another thing to keep in mind is that people move so you can get address forwarding information (NCOA - National Change of Address) for the past X timeframes if it's important that you have current address data. When you move, the address forwarding paperwork is only good for a set period of time and anyone who sends you mail after that window would get a Return to Sender, not at this address bounceback. NCOA'ing the mail before you send it would ensure you have current address, even if the forwarding has expired.

Our approach was to make a hash out of the standardized address (line 1 + postal code) and we'd use that as a comparison key.

Phone

The only tricky things with regard to phone was whether they had an area code associated with them. We stored them without separators or formatting and any extensions were stored separately. This boiled down to a 7 or 10 digit phone number. If we had an address, there is software that can usually backfill the area code. As area codes split, there is usually a grace period where a location could be served by 2 (or more) area codes.

Email

Generally speaking, an email address matches or it doesn't. When we were really desperate to try and match, we'd clean up our data. This involved looking at domains and ensuring they exist and adding the top level domain if they didn't exist. If we saw joan@aol it was a safe bet they meant @aol.com The other trick you can use to increase email matching is when they use + in their address. Some providers, like google, allow for joan+superuser@gmail.com to be delivered to the base address. I find it's helpful way of associating an email address to a specific site I've registered at. If junk mail starts flowing in on that account, then I know who's butt I can chew. But, for matching purposes you might be able to discard the contents from + to the @

Names

"What's in a name? That which we call a Jones by any other spelling could be the same person"

William Matchspeare

We found that there were two different types of matching we needed to perform on Names. Business or entity name and an individual's name. A US name might have a prefix (Mr, Mrs, Dr, Fr, Sen, Sgt, etc), a first name, middle, a second middle name or paternal surname, last name/maternal surname, generational (Jr, Sr, IV), professional/honorary/academic (MBA, JD, PhD, esq, etc). Isn't that fun?

It's usually not that bad as long as data has been captured in the individual parts. Otherwise, you can get weird results if you assume you can split on whitespace to determine name parts as my friend with a last name of "de los santos" can attest to.

Company names, well that's usually just what they give you. Things to be aware of are DBA-doing business as. "Soulless megacorporation LLC DBA Happy cuddly puppy preserve" That might need to match "Happy cuddly puppy preserve" and/or "Soulless Megacorporation"

Name matching

A first pass at personal name matching would be soundex. It's generally available in an RDBMS and it may be passable based on your input data. The problem with soundex is that it's only good for a subset of European names. A smarter phonetic approach and one we used was the Double Metaphone algorithm. This provided a much better result for string matching.

In our example above, an exact match on Jones to Joanes will fail but a phonetic match should catch. The problem though is we have Bobbie to Robert. No stretch of the imagination will make those two sound alike but the clients insisted we were missing matches so we added another set of checks to expand nicknames back to their full value and then re-ran comparisons.

In the company name comparisons, we found it was useful to compile a list of "stop words" - meaningless cruft that appears in names but should be ignored for match purposes (a, of, the, LLC, corp, univ, university)

We were then getting feedback that "simple" typos, transposition or omission of letters resulted in non-matched entities. As this answer grows long, we also had feedback on company name matching failing on entities like "Johns used tire barn" to "Johns mega used tire barn". We ended up implementing a n-gram comparison and a token comparison algorithm to help address those scenarios. I have since talked others in the industry and they were proponents of using Levenshtein distance for determining string matching.

Relationship

A relationship was basically something else we knew to be true. One company ran a promotion where salespeople got spiff based on getting customer to fill out business reply cards. We had "John's used tire barn" list of employees and we needed to correlate incomplete name data back to that reference set. I only talk about here for completeness. For your problem, you'll be looking at Name and MoC matches.

Get it done already

The specifics of your implementation will depend on what your data looks like and how much time and money you want to put into the problem.

My general approach would start by importing both sets of data into your database. The data that has all the attributes is your reference set. The smaller set of data is your candidate set. On the candidate table, add a column that contains your reference set identifier. The following lacks in normalization but that's intended

CREATE TABLE 
    dbo.reference 
(
    reference_id int identity(1,1) NOT NULL PRIMARY KEY
,   name_prfix varchar(50) NULL
,   name_first varchar(50) NOT NULL
,   name_middle varchar(50) NULL
,   name_last varchar(50) NOT NULL
,   name_suffix varchar(20) NULL
,   company_name varchar(100) NULL
,   address_line1 varchar(70) NULL
,   address_line2 varchar(50) NULL
,   address_city varchar(50) NULL
,   address_state varchar(20) NULL
,   address_postalcode varchar(10) NULL
,   address_zip4 char(4) NULL
,   phone_number varchar(10) NULL
)

CREATE TABLE 
    dbo.candidate
(
    candidate_id int identity(1,1) NOT NULL PRIMARY KEY
,   name_prfix varchar(50) NULL
,   name_first varchar(50) NOT NULL
,   name_middle varchar(50) NULL
,   name_last varchar(50) NOT NULL
,   name_suffix varchar(20) NULL
,   company_name varchar(100) NULL
,   address_line1 varchar(70) NULL
,   address_line2 varchar(50) NULL
,   address_city varchar(50) NULL
,   address_state varchar(20) NULL
,   address_postalcode varchar(10) NULL
,   address_zip4 char(4) NULL
,   reference_id int 
)

Iterative TSQL

Step 1, direct matches. Anywhere that an exact match exists between Candidate and Reference, record that in Candidate.reference_id and it's now excluded from the process.

Step 2, direct matches with nickname expansion and/or stop word replacement

Step 3, address matches with fuzzy name matching (double metaphone + ngram + minimum edit distance)

Step 4, address matches with fuzzy nickname expansion and/or stop word replacement matching (double metaphone + ngram + minimum edit distance)

Step 5, examine remaining candidate pool for manual matching

SSIS

The Enterprise Edition of SSIS provides for Fuzzy logic capabilities. Basically, it'll do much the same as listed in the TSQL approach without the need for you to put together your own framework for name matching and all that.

The 2012 release of SSIS also provides for Data Quality services which would address getting your addresses cleaned as well as splitting names out into parts.

share|improve this answer
    
What a brilliantly thorough answer and for letting us know that you did go to some great length to spam us (joke :) ) –  Dave Rook Jan 4 '13 at 15:27
1  
@DaveRook You're most welcome :D I'm surprised this answer has been downvoted to prevent future readers from implementing it. Fortunately, unless you were a animal health professional, farmer, golf industry professional or bought a specific brand of tires (tyres), I probably didn't spam you. Plus, you're in the UK. We had enough trouble with incorporating Canadian and Mexican addresses. No way in hell we'd be want to handle UK ones ;) –  billinkc Jan 4 '13 at 16:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.