View Full Version : Partial Match Search Query

03-20-2013, 05:57 PM
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.:thumbsup:

03-20-2013, 06:34 PM
Well, MySQL has full text search with query expansion:


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.

Old Pedant
03-21-2013, 01:02 AM
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?

03-21-2013, 02:39 PM
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.

Old Pedant
03-21-2013, 08:47 PM
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.