MYSQL Search/Wildcards

Question

I trying to search a MySQL database using PHP, The search works fine but I'm looking for a little help with wildcards:

The data in the field (Model) I am searching is: "A4" (215 results)

My search string is:

SELECT * FROM `temp_comp`.`mvl` WHERE `Model` LIKE '%A4 Avant%'

Is there a way I can still search 'A4 Avant' but this will return any fields that contain 'A4' or 'Avant'

The search term is taken from a csv file so I wanted to try and do this without having to split the two words first and search for 'A4' and/or 'Avant', I have tried the following but get no results:

SELECT * FROM `temp_comp`.`mvl` WHERE `Model` LIKE '%A4%Avant%'

As you may have guessed this is not my normal field so any help would be very much appreciated.

Answer 1

SELECT * FROM temp_comp.mvl WHERE (Model LIKE '%A4%') OR (Model LIKE '%Avant%')

If you want to avoid splitting up the test you can use a regexp:

SELECT * FROM temp_comp.mvl WHERE Model REGEXP 'A4|Avant'  <<-- Either '%A4% or %Avant%
SELECT * FROM temp_comp.mvl WHERE Model REGEXP 'A4*Avant'  <<-- '%A4%Avant% 

See the reference