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

Posted on 2009-04-15
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

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

    Author Comment

    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.  
    LVL 5

    Accepted Solution

    LVL 35

    Assisted Solution

    by:David Todd

    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


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now