CODEWITHSUNDEEP
mysqlsqlsearchsql-like
penpen
penpen

Reputation: 935

LIKE wildcard with multiple fields and spaces in MYSQL

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:

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

Upvotes: 8

Views: 49706

Answers (3)

Matteo Noonan
Matteo Noonan

Reputation: 1

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

Upvotes: -1

mu is too short
mu is too short

Reputation: 434965

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.

Upvotes: 9

Banjoe
Banjoe

Reputation: 1768

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?

Upvotes: 2

Related Questions