How to search two words in the same Mysql field

Question

Good morning.

I have a field on my table called tags, would like to do a search, where two or three words can be considered.

Example: In the field tags, I have some words, like "free", "cabins", "audiometer"... with the query below, my result returns empty, even with the word being inside the field.

Like I’m doing:

SELECT * 
FROM Produtos 
WHERE Tags = 'Audiômetro' OR 'Impedanciômetro' OR 'OEA' OR 'BERA' OR 'Vectonistagmógrafo';

My result only appears if I have ONLY one item in the "tags" field. If I put a phrase, for example, I get no results.

How to proceed?

Answer 1

First of all, the ideal would be if the tag text were separated from the field, it would be a more scalable solution.

If you really want to use textual search, the solution goes through something like this:

SELECT .... WHERE
     tags LIKE '%tag1%' AND
     tags LIKE '%tag2%' 

and repeats AND as many tags as possible.

There’s another problem to solve:

If the search is by the tag "Audio", you will find the field "Audiometer", which is undesirable.

The solution would be to use the field separator in the LIKE:

 SELECT ... WHERE
      CONCAT( ',' , tags , ',' ) LIKE '%,tag1,%' ...

This whole complexity serves to force the delimitation by commas, and the CONCAT to ensure that you have one at the beginning and one at the end (for the first and last tags).

FIND_IN_SET

If the tag field can be separated by a comma, Mysql has a dedicated function:

SELECT ... WHERE
    FIND_IN_SET( 'tag1', tags ) AND
    FIND_IN_SET( 'tag2', tags ) AND
    FIND_IN_SET( 'tag3', tags ) 
    ...

In any adopted solution, you should always normalize the storage of tags, to remove any spaces, hit accents etc.

Answer 2

You must use the clause IN:

SELECT * 
FROM Produtos 
WHERE Tags IN ('Audiômetro', 'Impedanciômetro', 'OEA', 'BERA', 'Vectonistagmógrafo');

edited

whereas the countryside Tags contains more than one tag the query would look a little different, see if the REGEXP makes things easier:

SELECT * 
FROM Produtos 
WHERE Tags REGEXP 'Audiômetro|Impedanciômetro|OEA|BERA|Vectonistagmógrafo';