Full text address matching

I'm looking for duplicate records. I开发者_运维百科 have a Property table with the fields street, number, city, state, county and zip. They get geo-coded based on location, but there are some holes in the data. Problem is if they make a simple typing error or omit certain fields, they won't come up as matches.

As of now a straight = comparison and LIKE aren't really doing a very good job. But Jaro Winkler and similar edit distance algorithms are running with extremely poor performance.

---

The CASS-Certified Scrubbing Service from SmartyStreets offers deduplication as part of their address verification process. Just upload the data in a delimited text file and the duplicates will be marked on the output file you download. There's always a free preview for each file you process so you don't have to purchase anything before you're satisfied with the results. I'm a software developer for SmartyStreets and helped write the application. I'm pretty pleased with both its functionality and ease of use. We also have an API you could use but the deduplication would be your responsibility (just compare the full, 12-digit Delivery Point Barcode, which serves as a unique identifier for addresses).