Geek Answers Handbook

MySQL finds umlaute "oe", "ae", "ue"

I am trying to build a MySQL search query. LIKE would be fine, but the client wants the inputs with "oe" to find "ö", "ae" to find "ä" and "ue" to find "ü", as is quite common in Germany.
I tried using REGEXP after replacing each occurrence of "oe" with (oe|ö) , but REGEXP is strict and does not match (for example) "é" with "e".
Is there a way to make LIKE match "oe | ue | ae" or maybe in some other way that I don't know? " Thanks,
Thomas

+5
source share
2 answers

In the Character Sets and Sorts Supported by MySQL I can only notice two German mappings:

  • latin1_german1_ci
  • latin1_german2_ci

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

latin1_german2_ci (phone book):

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

If your table / column is not yet used, you can force this sorting in the query itself, for example:

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

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

Disclaimer: I do not know anything about him. There may be another language that uses similar rules.

+8
source

If you are using utf8, COLLATE utf8_german2_ci . See the sorting chart (And, according to this chart, german2 is the only one satisfying your needs.)

 mysql> SELECT "oe" = "ö" COLLATE utf8_german2_ci; +-------------------------------------+ | "oe" = "ö" COLLATE utf8_german2_ci | +-------------------------------------+ | 1 | +-------------------------------------+

However, declaring COLLATE utf8_german2_ci column (s) is much more efficient than using this clause when comparing.

(If you are using utf8mb4, change the spelling accordingly.)

0
source

More articles:


All Articles