How can I ignore case sensitivity in the query in JPA?

Question

I have a small question. I have the following query that should filter by externalId or carName. The query also works but the query is case sensitive. But the filter should ignore upper and lower case.

@Query("SELECT d FROM CarLocationEntityView d WHERE d.externalId LIKE %:carNameAndExternalId% OR d.carName LIKE %:carNameAndExternalId% ")
   List<CarLocationEntityView> findByCarNameAndExternalId(String carNameAndExternalId);

I am working with JPA and a Postgres Sql database.

FIRST TRY:

@Query("SELECT d FROM CarLocationEntityView d WHERE d.externalId LIKE %:carNameAndExternalId% OR d.carName LIKE '%:carNameAndExternalId% COLLATE latin1_general_cs' ")
   List<CarLocationEntityView> findByCarNameAndExternalId(String carNameAndExternalId);

Does not work

SECOND TRY:

@Query("SELECT d FROM CarLocationEntityView d WHERE d.externalId LIKE %:carNameAndExternalId% OR d.carName LIKE low(%:carNameAndExternalId% )")
   List<CarLocationEntityView> findByCarNameAndExternalId(String carNameAndExternalId);

Does not work either

THIRD TRY:

@Query("SELECT d FROM CarLocationEntityView d WHERE d.externalId LIKE %:carNameAndExternalId% OR d.carName ILIKE %:carNameAndExternalId% ")
   List<CarLocationEntityView> findByCarNameAndExternalId(String carNameAndExternalId);

Does not work either

see https://stackoverflow.com/a/41988897/496099 – Chris Oct 22 '21 at 14:11 — Chris, Oct 22 '21 at 14:11

score 2 · Accepted Answer · answered Oct 22 '21 at 14:11

JPQL 2.1 added FUNCTION to call DB specific operations, but ILIKE wouldn't work in it. There are some JPA provider specific options, but generally I see projects just use lower on strings, similar to your second try, but on both strings for case insensitive comparisons. Something like:

"SELECT d FROM CarLocationEntityView d WHERE d.externalId LIKE %:carNameAndExternalId% OR lower(d.carName) LIKE lower(concat('%', :carNameAndExternalId,'%') )"

sorry for the late reply. But this has worked for me. – Doncarlito87 Nov 05 '21 at 08:16 — Doncarlito87, Nov 05 '21 at 08:16

score 0 · Answer 2 · answered Oct 22 '21 at 11:43

0

try ILIKE instead of LIKE, should work, see the documentation here:

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

answered Oct 22 '21 at 11:43

Edouard

6,577
1
9
20

Thanks for your answer, but it seems that JPA does not accept ILIKE. – Doncarlito87 Oct 22 '21 at 11:50

How can I ignore case sensitivity in the query in JPA?

2 Answers2