How to do a like case-insensitive and accent insensitive in PostgreSQL and JPA 2?
jakarta-ee, jpa-2.0, postgresql, postgresql-9.1
Solution
In general there is no standard way to write "accent-insensitive" code, or to compare words for equality while ignoring accents. The whole idea makes very little sense, as different accented characters mean different things in different languages/dialects, and their "plain ascii" substitutions/expansions vary by language. Please don't do this; `resume` and `résumé` are different words, and the situation gets even worse when considering any language(s) other than English.
For case-insensitivity you can use `lower(the_col) like lower('%match_expression')` in JPQL. As far as I know `ilike` isn't supported in JPQL, but I have not checked the standard to verify this. It's fairly readable, so consider just downloading the JPA2 spec and reading it. JPA2 Criteria offers `Restrictions.ilike` for the purpose. Neither will normalize/strip/ignore accented characters.
For stripping accents, etc, you will probably need to use database-engine specific stored functions or native queries. See, eg this prior answer, or if you intended to substitute accented characters with an unaccented alternative this PostgreSQL wiki entry - but again, please don't do this except for very limited purposes like finding places where words may've been "unaccented" by misguided software or users.
Problem
I have a Java EE project using PostgreSQL 9.X and JPA2 (Hibernate implementation). How can I force a like query to be case insensitive and accent insensitive? I'm able to change the charset of the DB because it's the first project using it.