Getting UTF8 values from SELECT ... LIKE request in Mysql

Question

In a PHP script I am doing the (hardcoded) MySQL-Request:

SELECT text FROM `mytexts` WHERE text LIKE '%ö%'

which wrongly returns the text "willkommen" (but not the text "willkammen") as well.

I have tried to have everything set correctly for UTF8:

• The source code file is in "UTF8 without BOM".

• Database, tables and fields (except the numeric fields) are collated as "utf8_unicode_ci".

• The connection is done by $server = mysqli_connect($dbhostname,$dbuser,$dbpass,$dbname); followed by mysqli_set_charset($server, "utf8");

• The data ("willkommen" and "willkammen") has been entered by a hardcoded INSERT

What do I have to do to get my SELECT work correctly?

Answer 1

You probably have to set another collation, one that doesn't treat ö==o but is still case insensitive.

SHOW COLLATION

shows you all available collations.
(_utf8 x'c3b6' is the character ö as unicode hex literal:)

select _utf8 x'c3b6' COLLATE utf8_general_ci LIKE 'o'

-> 1. This collation makes no difference between Umlauten and their corresponding base vowel counterparts.

select _utf8 x'c3b6' COLLATE utf8_bin LIKE 'o'

-> 0. Ok, but this one's not case insensitive.

select _utf8 x'c3b6' COLLATE utf8_german2_ci LIKE 'o'

-> 0. This one is my best guess.

Answer 2

Here are some of the equavalences for utf8 (or utf8mb4):

utf8 : utf8_croatian_ci            O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_czech_ci               O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_danish_ci              O=o=º=Ò=Ó=Ó=Ô=Õ=ò=ó=ó=ô=õ          oe=Œ=œ      oz
utf8 : utf8_esperanto_ci           O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_estonian_ci            O=o=º=Ò=Ó=Ó=Ô=ò=ó=ó=ô              oe=Œ=œ      oz           Ø=ø
utf8 : utf8_general_ci             O=o=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö        oe          oz
utf8 : utf8_general_mysql500_ci    O=o=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö        oe          oz
utf8 : utf8_german2_ci             O=o=º=Ò=Ó=Ó=Ô=Õ=ò=ó=ó=ô=õ          oe=Ö=ö=Œ=œ  oz           Ø=ø
utf8 : utf8_hungarian_ci           O=o=º=Ò=Ó=Ó=Ô=Õ=ò=ó=ó=ô=õ          oe=Œ=œ      oz  Ö=ö      Ø=ø
utf8 : utf8_icelandic_ci           O=o=º=Ò=Ô=Õ=ò=ô=õ                  oe=Œ=œ      oz  Ó=Ó=ó=ó
utf8 : utf8_latvian_ci             O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_lithuanian_ci          O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_persian_ci             O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_polish_ci              O=o=º=Ò=Ô=Õ=Ö=ò=ô=õ=ö              oe=Œ=œ      oz  Ó=Ó=ó=ó  Ø=ø
utf8 : utf8_roman_ci               O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_romanian_ci            O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_sinhala_ci             O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_slovak_ci              O=o=º=Ò=Ó=Ó=Õ=Ö=ò=ó=ó=õ=ö          oe=Œ=œ      oz  Ô=ô      Ø=ø
utf8 : utf8_slovenian_ci           O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_spanish2_ci            O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_spanish_ci             O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_swedish_ci             O=o=º=Ò=Ó=Ó=Ô=Õ=ò=ó=ó=ô=õ          oe=Œ=œ      oz
utf8 : utf8_turkish_ci             O=o=º=Ò=Ó=Ó=Ô=Õ=ò=ó=ó=ô=õ          oe=Œ=œ      oz  Ö=ö      Ø=ø
utf8 : utf8_unicode_520_ci         O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=Ø=ò=ó=ó=ô=õ=ö=ø  oe=Œ=œ      oz
utf8 : utf8_unicode_ci             O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø
utf8 : utf8_vietnamese_ci          O=o=º=Ò=Ó=Ó=Õ=Ö=ò=ó=ó=õ=ö          oe=Œ=œ      oz  Ô=ô      Ø=ø

It says that, for most collations, ö is treated identical to o. However, hungarian_ci and turkish_ci sort ö as if it were a letter between o and p. And german2_ci treats it equal to oe and œ. For danish_ci, icelandic_ci and swedish_ci, it comes after z.

More collations details