MySQL LIKE statement inteprets "o" and "ö" as the same

Question

I have a Rails 3 application connected to a MySQL-database. The encoding used is utf-8. The database connects a lot of data in Swedish and has a search function.

When I search for gotland (a Swedish island) results for Östergötland (a shire) is returned as well. Apparently MySQL interprets ö as o.

Is there a simple way to make sure that location LIKE '%gotland%' does not return fields containing götland?

Cheers.

Answer 1

I believe that by adding COLLATE utf8_swedish_ci after the LIKE statement, you will get what you want.

SELECT * FROM places WHERE name LIKE '%gotland%' COLLATE utf8_swedish_ci;

Alternately, you might want to use latin1_swedish_ci if latin1 is your character set.

I am not 100% certain that this fixes character comparison in a LIKE statement, but it logically should.

Sources:

• MySQL Manual
• Collation charts