Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jan 2013
    Posts
    17
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Partial Match Search Query

    Hi Folks

    I've asked a few questions on this forum now and the response has been absolutely magic. I feel like I'm taking the mick a bit so I will do my best to help with anyone else's questions as they come up to return the favour.

    Here's my latest one:

    I need to run a query on a column in my table to find partially matching fields, so where the column contains fields with "D.Brown" and "Dave Brown" I need the query to pick those fields up and return them as similar fields.

    I think this is a really complex query to run as it isn't as simple as when you have a reference to compare with using LIKE '%Brown%'. I need the query to "detect" matches using some kind of percentage comparison or REGEXP maybe. I'm using workbench if anyone knows of a plugin that could assist.

    Any advice would be gratefully received.

    Thanks in advance.

  • #2
    New Coder
    Join Date
    Jan 2008
    Posts
    63
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Well, MySQL has full text search with query expansion:

    http://dev.mysql.com/doc/refman/5.0/...expansion.html

    However I'm not sure how useful it'll be in this situation. You might have to do something like generate a couple queries one with the full name and one with the first initial and last name.
    Web hosting by coders, for coders: php hosting - Magento eCommerce hosting - perl hosting - check us out!

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    I can't think of any database query--or any computer language, for that matter--that would see "D.Brown" and "Dave Brown" as the same, or even as very similar.

    Yes, you could write code that would split them both apart, producing "D." and "Brown" versus "Dave" and "Brown", and presumably then find that the two "Brown"s matched, but "D." versus "Dave" wouldn't match much better than "Vern" versus "Dave" (at least with Vern and Dave, two letters match, not just one). Granted, you could prejudice the comparison to, say, insist that the first letters will be the same.

    But then let's say you have "D. Brown", "Dave Brown", "Dan Brown", and "Dale Brown". How would you expect the computer to pick the best pair match of those? Or should it even pick a match from those, at all?

    For that matter, how would a human being pick the best pair match from those four?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New Coder
    Join Date
    Jan 2013
    Posts
    17
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Yeh, I know it was a very long shot. I was hoping there might be some very clever type of query that might spot the "Brown" bit in both entries and return them both as a partial match.

    I really appreciate the feedback in any case.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Again, yes we could find the match on "Brown", but if you have many people with a last name of "Brown" and differing first names, the likelihood of getting the match "right" goes way down. In general, if you can think of a way to tell a human being how to find a match, then we can translate it into computer language. But if your human method isn't 100% reliable, the computer version won't be any better.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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