• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

Identify customer addresses that are similiar - but not an exact match

What is the best syntax to use to find customer addresses that have a common element? To identify same mailing address elements, where the address elements use different abbreviations, or in different formats.
2 Solutions
Depends on how you store the address in table
What are the columns you have
BOATFANAuthor Commented:
The column definition that I have to work with is a varchar(100) for Street1, Street2, Street3, City.  The State is varchar(5) and zip is varchar(15).  Zip is pretty standard.  US State codes are pretty standardized because they come from a web interface that forces them to be in the same format.  
David ToddSenior DBACommented:

There are better soundex functions than the ones in MS SQL, well, at least better than SQL 2000.

Do bear in mind that each soundex function has their one peculiarities. That is, when comparing streets they might be fooled by the street number unit/apartment, so you might have to parse that separately.

Search for soundex functions - they don't appear to be that hard to impliment yourself. Of course this is string manipulation, and not exactly what SQL does best, so a CLR implimentation might give better performance.

BOATFANAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now