queries mysql ignore tildes


I wanted to see if there is any way to search within a document so that it does not take into account the accents of the word. That is, if I want to search for the word "truck" in the text, I can do it by typing "truck" in the search window.

example I have this part of my PHP code Code:

SELECT * FROM anuncio WHERE tipo like  '%".$busqueda."%';

If I look for "Food", "measure", "omi" .... everything works perfect, but if I look for "food" with C minuscule does not detect, how can I make the query to throw it to me, regardless of whether they are uppercase? lowercase or accents? thanks !!

asked by andy gibbs 27.11.2018 в 05:13

2 answers


I see that you talk about two subjects: the question of the letters with accent and the one of the uppercase and lowercase letters. I had enough problems with both points in a very old MySQL database, and I solved them by coding all the data that could have literals (char, varchar, text ...) in UTF-8. In the structure of the table, I had those fields with a Latin encoding (I do not remember exactly which of the Latin was, but it was not UTF-8).

I modified the structure, putting the collation to utf8-general-ci (that of ci refers to Case Insensitive, that is, insensitive to uppercase and lowercase). If there are many tables, with large structures, it can be a bit cumbersome to touch them up, but it is better.

With that the two problems were solved at the same time. Maybe you like to read the article in link , because it details you as configure the MySQL engine so that UTF-8 is the default collation.

answered by 22.12.2018 в 22:03

for the search in the base omitting the tildes use:

SELECT * FROM anuncio WHERE type like '% truck%' COLLATE utf8_bin

So look for truck and truck

answered by 23.12.2018 в 00:59