How to also treat accented strings and without accents in a LIKE

Question

I’m performing a query where I need to search the data based on the first letter of a string, for example.

Accessories; select * from tabela where column LIKE 'a%'

However, if I pass some lyrics that have accent registered in the comic I can not restore using only the letter without the accent, for example o% and Ó%, interestingly when I do the display of data separated by alphabetical order the words with acute accent Ó they appear next to the letter a.

How can I fix this?

Answer 1

You need to use the correct collation in your database.

For example, if the column of your table that will be searched is in UTF-8, you need to use the collation utf8_unicode_ci ("ci" at the end of Mysql’s collations is "case insensitive", i.e., upper and lower case has the same weight).

To know the collations available, just this:

SHOW CHARACTER SET;

Output example:

+----------+-----------------------------+---------------------+
| Charset  | Description                 | Default collation   |
+----------+-----------------------------+---------------------+
| latin1   | cp1252 West European        | latin1_swedish_ci   |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |
| ascii    | US ASCII                    | ascii_general_ci    |
| cp1250   | Windows Central European    | cp1250_general_ci   |

... truncado para melhor leitura. deixei os mais comuns pra nós ...

| utf8     | UTF-8 Unicode               | utf8_general_ci     |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |
| cp852    | DOS Central European        | cp852_general_ci    |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |
+----------+-----------------------------+---------------------+

For test purposes, Victor’s solution is good, but for definitive use, it is necessary to modify the table or column so that it does not need the COLLATE in query.

Use COLLATE in query is only for exceptional and punctual solutions, when the default is not suitable for situations of exception.

The Handbook describes the whole collations, but it’s in English:

https://dev.mysql.com/doc/refman/5.7/en/charset.html

Converting charset from table

To change the collation of a table:

ALTER TABLE <nome> COLLATE utf8_unicode_ci; -- ponha o collation correto

To convert the charset of a table:

CLOSE ATTENTION! NEVER use the command below if the table is not behaving properly. Once everything is right, then you may need to convert the data, and only then can you use this option, because it moves the data of the table. ALWAYS BACK UP. Ready, now you can put the mouse on top of Marelinho to see the command :)

ALTER TABLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Answer 2

Try the following:

SELECT * FROM tabela WHERE coluna LIKE 'a%' COLLATE utf8_unicode_ci;