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

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

CodePudding user response：

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,'%') )"

CodePudding user response：

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.