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.

Acanthocephalan

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.

Aristate

It's looking to be a bit more complicated than that but I think you're right about the full-text search, I hadn't looked into that. Thank you for taking the time to help me. – Acanthocephalan

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?

Fizzy

Thanks, gave it a quick try but not getting anywhere. Think I'll take a closer look when I get a chance – Acanthocephalan

Answer 3

In this case what is the difference between using LIKE '%Ford Mustang%' and LIKE 'Ford Mustang%'

Quinze

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Necropsy