Two partial MySQL matches in a concat_ws?

Question

Here's my use case: I'm searching for a person by first and last name, but only type in a partial first and partial last name, how can I create a WHERE clause that catches all possible scenarios?

Example, I type "Joe Smith" and it has a result. I type "Joe" and it has Joe Smith and a few other Joe's. I type "Joe Sm" and it gives me Joe Smith.

I want to be able to type "J Smit" and get Joe Smith, is that possible? Do I need to break the search term on spaces in PHP before doing a LIKE?

Here's what I have so far that works with full matches:

WHERE CONCAT_WS(' ', owner.first_name, owner.last_name)  
LIKE '%". $searchTerm ."%'

Any help would be greatly appreciated.

You actually need to avoid concatenating because the concatenated string doesn't allow for the `LIKE` to work where the space is placed between the words. You will need to split the search terms so they can be applied to the query in an `OR` condition. — Jay Blanchard, Dec 29 '14 at 14:52

KIKO Software · Accepted Answer · 2014-12-29T14:57:37.573

Why don't you do an explode(' ',$input) on your input in PHP and then compare all values of that array in your WHERE clause?

$inputArray = explode(' ',$input);
foreach ($inputArray as $part)
{
  $whereArray = "CONCAT_WS(' ',owner.first_name,owner.last_name) LIKE '%$part%'";
}
$where = implode(' AND ',$whereArray);

And then use it like this:

$query = "SELECT * FROM owner WHERE $where";

Please pay attention to security, I didn't do that.

This still doesn't quite do what you want. Because when you want to search for "J Smit" you want the system to be intelligent enough, to search one part, say "J" in the first name column and the other part "Smit" in the last name column. Clearly that's more complex, and the complexity increases with the number of parts to match. There is a solution for that, but you won't like it, it's ugly.

Has anybody got a, not so ugly, solution to this?

Alright, that makes sense. Wish there was a native MySQL command for this, was looking at RLIKE for regular expression matching, but couldn't find anything that worked for me. — Adam Fehnel, Dec 29 '14 at 14:55

score 0 · Answer 2 · edited May 23 '17 at 11:57

It sounds like you do want split the search term into a first and last name component, and then run LIKE comparisons against owner.first_name and owner.last_name separately. Unfortunately, I don't know of native mySQL support for straightforward string splitting.

Splitting in PHP first is certainly an option (the answer from @KIKOSoftware seems to do a good job of that). If you want to try to do it all in mySQL as an alternative, this SO question offers some insight (you will have to modify for your use case, since you're delimiting on white space instead of commas):

How to split the name string in mysql?

Two partial MySQL matches in a concat_ws?

2 Answers2