Search code examples
mysqlsqlmariadbspecial-characterssql-like

searching for german characters with sql using LIKE condition

I'm working with a database where german names are used, i.e. Fürst. Now I built a HTML-Form with a searchfield. Used htmlentities and some tests to verified no "bad stuff" are entered and then html_entity_decode before the query. A simple query would then looks like:

SELECT * FROM user_table WHERE firstname LIKE "%fü%" OR lastname LIKE "%fü%"

However, this find not only Fürst, but Furtas well. How can I fix this? The database and the tables are in utf8_unicode_ci and have to stay that way.

Solution

  • Here are the collations for each charset

    SELECT * FROM user_table WHERE firstname LIKE "%fü%" COLLATE utf8mb4_german2_ci            
                          OR lastname LIKE "%fü%" COLLATE utf8mb4_german2_ci

    utf8mb4_unicode_ci would probably work as well.