How to ignore accents when querying string paths?

[Jonathan Rodrigues de Oliveira]

There is some way to ignore accents on queries?

Example: I have a person named as "José" in my database. I want to my query do return that person accepting "jose" or "josé" as parameter for the name:

JPQLQuery query = new JPAQuery(entityManager);
query.from(person);
query.where(person.name.likeIgnoreAccents("jose"));

How can I do this?
Thanks.

[timowest]

This is not supported directly on the JPA level. What database do you use and how would you express that on the SQL level?

[Jonathan Rodrigues de Oliveira]

I'm using Postgresql and I would use the "unaccent" function.

http://www.postgresql.org/docs/9.4/static/unaccent.html

[Jonathan Rodrigues de Oliveira]

Any idea on this?

On Thursday, October 1, 2015 at 12:41:37 PM UTC-3, timowest wrote:

[timowest]

You can do that using JPA 2.1 SQL function invocations:

StringExpression expr = Expressions.stringTemplate("FUNCTION('unaccent', {0})", str);

where str is the original expression and expr the resulting unaccent invocation.