Can't use Ecto's ilike with german ü

Questions / Help
, ,
1

I have a query that uses dynamic([c], ilike(c.email, ^term)) to search for customers by fuzzy searching on email, but ecto/postgres is throwing the following error for queries that include the german umlaut ü with the following message.

** (Postgrex.Error) ERROR 22021 (character_not_in_repertoire) invalid byte sequence for encoding "UTF8": 0xc3 0x25
    (ecto_sql 3.11.3) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1

This is my db encoding config

Screenshot 2024-08-30 at 1.14.04 PM
Screenshot 2024-08-30 at 1.14.04 PM1350×446 24.4 KB

Has anyone seen / solved this before ?

Any help would be much appreciated!

2

The byte sequence complaint isn’t about an u-umlaut. It’s actually Ã%. So it looks like maybe an encoding issue elsewhere in the target text - or maybe even the text in the database isn’t actually UTF-8.

3 Likes
3

you are right. A previous cleanup step using regex was replacing “non-word” characters with %, which ü is. Had to change it to String.replace(value, ~r/[^\wäöüÄÖÜß]+/, "%") thanks!