JPQL query not working for case insensitive parameters

Question

I am trying to write a JPQL query for searching data based on String. when i am trying to search data with 'C' it works fine, but its not working for 'c', below is the query. for example: below query is working for Cat, College, Collapse, but not working for cat, college, collapse. any help is appreciate.

@Query("select ca.company_01 from CompanyAddress_02 ca WHERE ca.statusActiveSwitch = 'Y' AND ca.country.region.regionCode = :regionCode")
    Page<Company_01> findAllCompany_01Region(@Param("regionCode") String regionCode,  Pageable pageRequest);

see https://stackoverflow.com/questions/37178520/jpql-like-case-insensitive — Scary Wombat, Feb 10 '20 at 06:58
@ScaryWombat Not an exact duplicate of that one, but nice catch, I couldn't find anything. — Tim Biegeleisen, Feb 10 '20 at 07:01

score 2 · Accepted Answer · answered Feb 10 '20 at 06:58

One approach would be to just lowercase both sides of the region code when comparing in your JPQL query:

@Query("SELECT ca.company_01 FROM CompanyAddress_02 ca WHERE ca.statusActiveSwitch = 'Y' AND LOWER(ca.country.region.regionCode) = LOWER(:regionCode)")
Page<Company_01> findAllCompany_01Region(@Param("regionCode") String regionCode,  Pageable pageRequest);

Note that if you don't really have any use for the case in your region code, then consider just storing it all in a one case, e.g. lowercase, and then ensure that what you pass to your backend is also always the same case. This avoids calling LOWER(), which renders the WHERE clause non sargable (i.e. it cannot use an index).

JPQL query not working for case insensitive parameters

1 Answers1