JPA @Query with LIKE and UPPER %-CodePudding

I am trying to run the following query in JPA hibernate @Repostiory:

@Query(value = "select city, state_code "  
          "from mv_city_state_center "  
          "where upper(city) like upper(:citycriteria)%",
          nativeQuery = true)
List<String> searchCityStateCenter(@Param("citycriteria") String cityCriteria);

In particular, the line like upper(:citycriteria)%.

The citycriteria is a partial string like 'pit' and I want to first capitalize it to PIT and then put it in the like statement eg. where upper(city) like PIT% - which would match to PITTSBURGH and PITTCAIRN for example.

Hibernate accepts like pit% but when the upper is thrown in the mix, it fails for both:

like upper(:citycriteria)%
like upper(:citycriteria%)

What is the correct syntax for this? For now, I'm ensuring all caller methods just pass cityCriteria.toUpperCase() but would prefer if the query itself handled this so callers don't have to be concerned with it.

I also tried something like below from this guide to no avail.

public interface AuthorRepository extends JpaRepository<Author, Long> {
 
    @Query("FROM Author WHERE UPPER(firstName) LIKE %?#{[0].toUpperCase()}%")
    List<Author> findByFirstNameContainingIgnoreCase(String firstName);
}

CodePudding user response：

1 You are using a native query, why?

2 You return a List but the projection has two columns (city, state_code)!

To concatenate two strings you can use concat:

@Query(value = "select city, state_code "  
          "from mv_city_state_center "  
          "where upper(city) like upper(concat(:citycriteria, '%'))"
          nativeQuery = true)
List<String[]> searchCityStateCenter(@Param("citycriteria") String cityCriteria);