EDIT: if no such collation exists, I can work around it if MySQL has a function which will convert strings into ASCII equivalents e.g.
FUNC('Være')='Vaere' -- there might be several such functions, one for each alphabet, which would be a hassle but acceptable if effective.
I need a MySQL5 collation which will compare accented characters as equivalent, irrespective of accents. e.g. "fêter" should be considered equal to "feter" and "eł" should match "el"
Ideally this wouldn't be limited to single-character substitutions, e.g. "For At Være Som Man Bør" should be considered equal to "For At Vaere Som Man Bor".
WHERE name LIKE '%ere%' should also match it.
I see there are collations for spanish/polish/etc, but I need <strong>one</strong> collation to cover <strong>all</strong> latin-like alphabets (not Arabic or Asian ones etc). The strings are stored as UTF-8.
Which collation should I use?Answer1:
Since you said you're using the utf8 characters set, the answer to your original question is "all of them except utf8_bin".
In all of these collations you will find that 'e' = 'ê'.
However, I don't know of any collations where 'æ' = 'ae' or 'ø' = 'o'.
utf8_czech_ci utf8_danish_ci utf8_esperanto_ci utf8_estonian_ci utf8_general_ci utf8_hungarian_ci utf8_icelandic_ci utf8_latvian_ci utf8_lithuanian_ci utf8_persian_ci utf8_polish_ci utf8_roman_ci utf8_romanian_ci utf8_sinhala_ci utf8_slovak_ci utf8_slovenian_ci utf8_spanish_ci utf8_spanish2_ci utf8_swedish_ci utf8_turkish_ci utf8_unicode_ciAnswer2:
utf8-general-ci would be the closest one: <a href="http://www.collation-charts.org/mysql60/mysql604.utf8_general_ci.european.html" rel="nofollow">http://www.collation-charts.org/mysql60/mysql604.utf8_general_ci.european.html</a>
Also note that as of MySQL 5.0 you can create your own collations. <a href="http://dev.mysql.com/doc/refman/5.0/en/adding-collation.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/adding-collation.html</a> http://forge.mysql.com/wiki/How_to_Add_a_Collation