[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2009-04-15
Medium Priority
Last Modified: 2012-05-06
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.
Question by:BOATFAN
LVL 15

Expert Comment

ID: 24150735
Depends on how you store the address in table
What are the columns you have

Author Comment

ID: 24150871
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.  

Accepted Solution

bprojoe earned 750 total points
ID: 24151244
LVL 35

Assisted Solution

by:David Todd
David Todd earned 750 total points
ID: 24151771

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.


Author Closing Comment

ID: 31570596

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question