CODEWITHSUNDEEP
mysql
user1051894
user1051894

Reputation: 63

MYSQL Search/Wildcards

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.

Upvotes: 6

Views: 21928

Answers (3)

Johan
Johan

Reputation: 76537

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

Upvotes: 11

itsols
itsols

Reputation: 5582

Here's how you can achieve this.

Modify your query like this:

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

The above example will look for elements of the search term in the query in such a manner that they should all be present in the record. And if you want to search for records with either of the search terms, you can replace the word AND with OR and it does the job.

And ideally, you would follow these steps to prepare for the query:

  1. Take the input string to search.
  2. Split it by spaces so you have an array of words
  3. Loop through the array and build your search string.
  4. Use the search string in your query.

Upvotes: 0

Jim H.
Jim H.

Reputation: 5579

MySQL doesn't have a built-in way to split strings in a query. Splitting them beforehand and then adding n predicates to the where clause is trivial.

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

Upvotes: 0

Related Questions