Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: Compare two strings

    This is my issue.

    I have to tables.

    tblMembers:
    Name
    Address
    Postcode
    Town/City

    tblAddresses:
    Roadname
    Postcode
    Town/city

    tblMembers holds a list of all members of a club and their associated address. The other table (tblAddresses) holds a masterlist of all roads in the area of the club (please notice that there are several postcodes and cities in the area).

    What I now want to do is to compare the addresses of the members against the masterlist to correct any typing errors and postcodes that doesn't match the corresponding town.

    So far my considerations have lead me to consider the use of LIKE and some kind of pattern for the comparesion.

    Do you have any suggestions for how to handle this issue?

    I hope that I were able to make my self clead despite of my rather bad english abilities.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would do a straight forward exact compare to find all the ones you have that do not match. If this is not a substantial number then manually correct.

    If it is a substantial number then consider a third party data cleaning company.

    If you are really keen to DIY (and I can almost guarantee the ROI will not be there) then you will want to consider writing\ pinching-from-the-web some distance algorithms such as Levenstein, and soundex algorithms such as Metaphone. I would also advice you write these in managed code, not T-SQL.

    And once you have cleaned up your data I would consider redesigning your tables and putting in some constraints.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2005
    Posts
    122
    You could create a CLR function or procedure implementing Levenhstein distance or a similar algorithm to compare to strings for similarity.

    You'll probably find a lot of C# implementations if you google.

  4. #4
    Join Date
    May 2009
    Posts
    3
    Thanks a lot for the help.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, consider adding constraints to keep your data in line from here out
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •