Results 1 to 9 of 9

Thread: Alternate names

  1. #1
    Join Date
    Mar 2005
    Posts
    92

    Unanswered: Alternate names

    I need to implement support for alternate names on the database I’m working on.

    When a user looks up: “Antony Bigglesworth” the query would return both: Antony Bigglesworth as well as Tony Bigglesworth, would both exist.

    Anyone has a best practice on this or has done this before?

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    You mean hat it returns every first name that is a substring of the name you look for?
    Something like:

    Where SearchString Like '%' + FirstNameField + '%'

    I believe that would return, Antony, Tony, Ton, A, but not Abby and George.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I presume iot is more complex that that yes? "Dick" for "Richard" and "Teddy" for "Edward"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Mar 2005
    Posts
    92
    Quote Originally Posted by pootle flump
    I presume iot is more complex that that yes? "Dick" for "Richard" and "Teddy" for "Edward"?


    Yes, Antony clearly wasn’t the best choice.

    I need Richard Bigglesworth to be returned when I look for Dick Bigglesworth!

    Thanks for getting back on this.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use my fuzzy-search algorithm. It was created to do exactly what you describe.
    http://sqlblindman.googlepages.com/fuzzysearchalgorithm

    Example:
    Code:
    set nocount on
    select	dbo.comparetext('Antony Bigglesworth', 'Tony Bigglesworth') as CompareValue1
    select	dbo.comparetext('Richard Bigglesworth', 'Dick Bigglesworth') as CompareValue2
    
    Results:
    CompareValue1
    -------------
               94
    
    CompareValue2
    -------------
               74
    Last edited by blindman; 08-06-07 at 12:04.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Mar 2005
    Posts
    92
    Thanks Blindman,

    That looks great.

    I also have to define a group of names which would be specificly checked and collected then returned from the database. Thus, the user would look for "Tony" as the first name, and so all the people with first name "Tony" and all the people with the first name "Anthony" would be returned.

    I kind of did it now, created a table where you can add in these name groups, and a small sql script then checks if the sought after first name is in the table, if it is it gets the group id, then cycles through the group collecting all the relevant records then returns them.

    Thanks for all the help on this

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    To be thorough, you should accept the names "Buddy" and "Pal" and perhaps "Dude" as wild-cards that match any name.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    don't forget "Todd" and "Biff"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    In SoCal, we also have to expand a bit on Blindman's suggestion, since we also have "Duuuuuude!" as well as any name with the suffix "meister" appended (as in "Paulmeister" or "Blindmanmeister").
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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