Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Getting started with phonetic comparisons

    I've been reading a bit about full-text searches, phonetic values and match-queries and just don't know where to begin.

    What I'm eventually going to do, is make procedures for matching names, finding records that are close matches and presenting them in a subform below the actual member that you look up.

    E.g. if an employee looks up Sergej, he or she will also see Sergey, Sergei etc. below the membersheet.

    BOL isn't very practical in examples, and its about 7 years since I took my SQL-Server 7.0 MS courses, plus I've primarily worked as an administrator up until last fall, not a developer. So where to begin?

    Thanks in advance,

    Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Two functions in this attachment.

    Function COMPARETEXT() evaluates all the character pairs in two strings and returns a numeric value between 0 and 100 indicating the degree of similarity between the strings.

    Function MATCHTEXT() strips vowels and non-alphanumeric characters from a string to create a "matchstring" that can be passed to COMPARETEXT for more accurate comparisons.

    I have used these two functions to indentify duplicate records for ten years now. The most effective method is to create a permanent column of matchtext strings in your table, so as to prevent having to recalculate them. Then pass the string you want to compare through MATCHTEXT() prior to submitting it as a parameter to COMPARETEXT along with the data in your table.

    Generally, a returned value of over 80 indicates a match. Anything between 60 and 80 is worth having a human review. Also, the process goes faster if you can first match records on some other factor, such as zip-code.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    Man, this looks positively awesome. I've been looking at different approaches, and comparetext combined with the SQL function Difference will positively do for my situation of comparing names.

    Very slick and easy functions you've made there. Probably not easy to invent, but easy to understand.

    Thanks a bunch. Your name resides in another sql-server installation on the world

    Cheers, Trinsan
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Trinsan
    Thanks a bunch. Your name resides in another sql-server installation on the world
    You don't work for the CIA, I hope...

    Thanks for the kudos.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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