Search code examples
mysqlsqlsql-likesql-search

SQL "like" search for characters like "öüä"?

In database fields are characters to html entities converted, so if I try to search for words like übung or ärzte nothing is found. I tried to add in my SQL query COLLATE utf8_general_ci but I get there following error: COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

My fields what I search are in utf8_general_ci encoding.

Is there possible to make such search or to convert by submit some letters to html entities?

Solution

  • https://dev.mysql.com/doc/refman/5.7/en/charset-literal.html

    A nonbinary string with latin1 character set and latin1_german1_ci collation:

    SELECT _latin1'übung' COLLATE latin1_german1_ci;

result
übung

    check your database collation

    https://makandracards.com/makandra/2529-show-and-change-mysql-default-character-set

    SHOW VARIABLES LIKE  'char%';

    I reckon you are set with latin1 so you can only use latin1 ... if utf8 then should allow you.