Matching External lists to your Database?

5 pts.
Tags:
Query optimization
SELECT statement
SQL
SQL queries
Hello everyone, I work in a telco and manage the data in the company's CRM. I'm often given lists of company names by our alliance team, and asked to match the list against our database to see which companies already exist in the CRM and which don't. At the moment I can only accomplish this by one-to-one matching, using a LIKE "%part of company name%" search in Access, examining the results, and choosing the closest match. The lists given to me by Alliance often have hand type names, using ampersands, excluding the INC off the end, incorrect spelling, inappropriate punctuation etc - which makes a SELECT WHERE 'company name' is equal query completely fruitless. My question is this - can anyone recommend string matching software or an SQL query that will wash one list against another, finding the closest match with confidence intervals? I don't know if this would involve fuzzy logic or not - but I'm convinced there needs to be a more efficient way to go about doing this. Any advice is greatly appreciated, thanks a lot for reading. Nik
ASKED: April 1, 2009  10:54 PM
UPDATED: April 3, 2009  12:23 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

How do i start. I also work fo a comapny where we have lists come in every day that we need to de-dupe from what in our SQL Server database. We get lists from Alliance as well a lot of other vndors so i know your pain. As far as software tools we use MatchIT made by QAS(qas.com). I’ve used it for two years and its very effective. I’ve also written a lot of my own procedures and queries. For the most part LIKE is not ver effective. You should look up CHARINDEX, SOUNDEX or DIFFERENCE functions if you are using SQL Server. Our problems range from obvious to same person differnt State or company is XYZ LTD in one place and XYZ CORP in another. thats why the MatchIT program is effective because it can still find these as a match and assign it a score and dump it to a file that you can look at and see if you think its a dupe and it works with all file types and databases. Hope this helps.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following