MySQL - match against accented

Question

I'm doing match against query, but I need to get accented items "éxample" when searching with unaccented 'example'.

Is there a way to do that?

Answer 1

Sure, what you're looking for is the UTF8 collation/encoding.

To have the perfect UTF8 environment and to make sure all your queries support such collation, make sure that:

• Your Database collation is in UTF8
• Your Table collation is in UTF8
• The Column containing the data with accents is in UTF8

You will see that there are a lot of UTF8 collations. The most comprehensive one is utf8mb4_unicode_ci. More info here

Once these 3 conditions are satisfied, you can run your REGEX like you would do for normal characters.

WARNING If your data is already in the database and you attempt to change the collation, you might lose or corrupt the content. This settings should be set before you start adding data to the database.