thomas thomas - 2 months ago 11x
MySQL Question

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

I'm trying to build a MySQL search query.

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
after replacing every occurence of "oe" to
is strict and doesn't match (for instance) "é" to "e".

Is there a way to make
match "oe|ue|ae" or maybe some other way I haven't though of?




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.