how to write JPQL query for similar characters

Question

I have a query to select similar entities from db.

Query query = entityManager.createQuery("select c from Case c where c.lastName = :lastName");

But I have a problem.

The 'lastname' value of an entity is 'SAĞLAM' in database and the parameter of the query is 'SAGLAM' or vice versa. So the query doesn't give the entity.

The situation occurs for C/Ç, S/Ş , G/Ğ , O/Ö , U/Ü, I/İ.

How can I solve this problem? Can I write a regex?

Answer 1

In JPQL you can't do this... But with hibernate (HQL), you can do this:

select  upper(convert('This is a têst','US7ASCII')),
        upper(convert('THIS is A test','US7ASCII'))
from dual;

select  1 from dual 
where upper(convert('This is a têst','US7ASCII')) =
             upper(convert('THIS is A test','US7ASCII'))

To do this search with another JPA implementation, you will need to use a nativeQuery and choose some of this options:

• change the collate of your database in the SELECT, like (SQL Server example): SELECT e.name FROM Entity e WHERE e.name COLLATE Latin1_General_CI_AI LIKE 'têst' COLLATE Latin1_General_CI_AI
• Use a native function of your database, like SOUNDEX
• Use the regex function of your database

If you really need to use JPQL:

• Create a new column in the table, removing the accents every time you INSERT or UPDATE the original column. Probably this is the best option for performance.