Spring Data JPA - escape accent french character (é, à, è, û..)

Question

I'm using Spring Data JPA method name query to do SQL query, such as findByNameContains(String name) to find all entities whose name contains a given string. But it only applies to exact characters. I'd like to know if a user enters é, how to return all entities whose name contain character é and its base character e. Thanks.

do you have special characters in the database, or just from the input of the user? — Youcef LAIDANI, Mar 19 '18 at 16:27
Yes, I want English character and its variants become interchangeable during searching. I have `Montreal` and `Montréal` in my DB, I want to return both names when user enter `Montreal` or `Montréal`. — JasminDan, Mar 19 '18 at 16:31

Youcef LAIDANI · Accepted Answer · 2018-03-19T17:21:35.880

5

I don't think this functionality exist in Spring Data.

Because you are using PostgreSQL you can use unaccent Extension It can help you in your case, beside you have to create a native custom query like this :

PostgresSQL

@Query(nativeQuery = true, value = "SELECT * FROM schema_name.table_name "
                                 + "WHERE unaccent(col_name) = unaccent(:name)")
public List<EntityName> find(@Param("name") String name);

Note : you have to Enable this unaccent extension in your database :

CREATE EXTENSION unaccent;

MySQL

For those who use MySQL you can use collate utf8_bin Like this :

@Query(nativeQuery = true, value = "SELECT * FROM table_name "
                                 + "WHERE col_name = :name collate utf8_bin")
public List<EntityName> find(@Param("name") String name);

SQL Server

For those who are using Oracle you can use collation

@Query(nativeQuery = true, value = "SELECT * FROM table_name WHERE "
        + "name COLLATE Latin1_general_CI_AI Like :name COLLATE Latin1_general_CI_AI")
public List<EntityName> find(@Param("name") String name);

References :

edited Mar 19 '18 at 17:21

answered Mar 19 '18 at 17:00

Youcef LAIDANI

55,661
15
90
140

Really appreciate your example. I succeeded to transform `findAllByNameStartingWithIgnoreCaseOrderByName` to `@Query(nativeQuery = true, value = "SELECT * FROM public.city " + "WHERE upper(unaccent(city_name)) LIKE upper(unaccent(:name) || '%')")`. – JasminDan Mar 20 '18 at 15:50
Are you familiar with spring batch? I have another question about a JDBC exception skipping in spring batch [here](https://stackoverflow.com/questions/49263616/spring-batch-error-tolerance). – JasminDan Mar 20 '18 at 17:01
@JasminDan I will take a look later, If I have an idea, I will share it with you – Youcef LAIDANI Mar 20 '18 at 17:03
What if you want to limit results using Pageable? – Kikin-Sama Sep 28 '20 at 17:23
@Kikin-Sama I will check this when I have some free time :) – Youcef LAIDANI Sep 29 '20 at 06:51

Spring Data JPA - escape accent french character (é, à, è, û..)

1 Answers1