LIKE wildcard with multiple fields and spaces in MYSQL

Question

I'm having some trouble searching for any similar match in two fields. For example I have a table with the values:

CAR MAKE   CAR MODEL
Ford       Mustang (Shelby)
Toyota     Corolla
Seat       Leon

etc etc.

I want to be able to get the result "Ford, Mustang (Shelby)" by searching for any of the following combinations:

• Ford

• Mustang

• Shelby

• Ford Mustang

  or any other combination.

Is this possible? I've had a good search but it's hard to find the search terms to describe what I mean.

Answer 1

Split your terms on whitespace and then, for each term, build a little bit of SQL like this:

car_make like '%x%' or car_model like '%x%'

Then join all of those with or to get your WHERE clause. So for "Shelby Ford", you'd end up with SQL like this:

select stuff
from cars
where car_make like '%Shelby%'
   or car_model like '%Shelby%'
   or car_make like '%Ford%'
   or car_model like '%Ford%'

If you need anything more complicated then investigate MySQL's full-text search capabilities.

Answer 2

Give this a try:

SELECT Car_Make, Car_Model, CONCAT_WS(' ', Car_Make, Car_Model) as Make_Model 
FROM cars 
WHERE CONCAT_WS(' ', Car_Make, Car_Model) LIKE '%Ford Mustang%'

Not sure of the exact syntax since I'm not at home but something similar should work.

See also: Using mysql concat() in WHERE clause?