How to treat accented and uncharged strings in LIKE too

1

I'm doing a query where I need to fetch the data based on the first letter of a string, for example.

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

But if I pass some letter that has accent registered in the bd I can not get it to use only the letter without the accent, for example o% and Ó% , curiously when I display the data separated by alphabetical order the words with acute accent Ó they appear next to the letter a .

How can I resolve this?

    
asked by anonymous 03.08.2016 / 15:24

2 answers

4

You need to use the correct collation in your database.

For example, if the column of your table that is going to be searched is in UTF-8, you need to use the collation utf8_unicode_ci ("ci" at the end of the MySQL collations is case insensitive, lowercase has the same weight).

To know the collations available, just do this:

SHOW CHARACTER SET;

Example output:

+----------+-----------------------------+---------------------+
| 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 testing purposes, Victor's solution is good, but for definite use, you may want to modify the table or column so that you do not need COLLATE in query .

p>

The Handbook describes well all the collations , but is in English:

  

COLLATE


Converting table charset

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:

VERY WARNING! NEVER use the command below if the table is not behaving correctly. Once that's all right, then you may need to convert the data, and only then can you use that option as it moves the data in the table. ALWAYS MAKE BACKUP. Ready, now you can mouse over the yellow to see the command:)

  

ALTER TABLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

    
03.08.2016 / 16:00
0

Try the following:

SELECT * FROM tabela WHERE coluna LIKE 'a%' COLLATE utf8_unicode_ci;
    
03.08.2016 / 15:35