MySQL find umlaute by "oe", "ae", "ue"

Question

I'm trying to build a MySQL search query. LIKE would be perfectly fine, but the client wants inputs with "oe" find "ö", "ae" find "ä" and "ue" find "ü" as it's fairly common in Germany.
I tried using REGEXP after replacing every occurence of "oe" to (oe|ö) but REGEXP is strict and doesn't match (for instance) "é" to "e".
Is there a way to make LIKE match "oe|ue|ae" or maybe some other way I haven't though of?
Thanks,
thomas

Answer 1

At Character Sets and Collations Supported by MySQL I can only spot two German collations:

• latin1_german1_ci
• latin1_german2_ci

It seems that latin1_german2_ci is the one you want, however it expects Latin1:

latin1_german2_ci (phone-book) rules:

• Ä = AE
• Ö = OE
• Ü = UE
• ß = ss

If your table/column is not already using it, you can force such collation in the query itself, e.g.:

mysql> SELECT _latin1'oe' collate latin1_german2_ci ='ö' AS are_equal;
+-----------+
| are_equal |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

If your application is using Latin1 this should do the trick. Otherwise, I honestly have no idea :)

Disclaimer: I know nothing about German. There may be another language that uses similar rules.