How to search for two words in the same field Mysql

Navigation
5

Good morning.

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

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

How do I do: 

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

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

How to proceed?

    
asked by anonymous 09.03.2018 / 15:12

2 answers

4

You should use the IN : 

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

edited

Since the Tags field contains more than one tag the query would look a bit different, see if REGEXP makes things easier: 

SELECT * 
FROM Produtos 
WHERE Tags REGEXP 'Audiômetro|Impedanciômetro|OEA|BERA|Vectonistagmógrafo';
    
09.03.2018 / 15:14
7

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

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

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

and repeat the AND as many tags as possible.

There's another problem to solve:

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

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

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

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


FIND_IN_SET

If the tags field can be separated by the 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 solution adopted, you should always normalize the storage of the tags, to remove any spaces, hit accents etc.

    
09.03.2018 / 15:20
How to put separate ggplot2 graphics, but on the same screen? How do I delete a file marked with the "read only" attribute?