1

I'm writing a select query using Spring JPA. I'm passing in parameters to compare to an entry in the database. Where I'm running into problems (doesn't compile) is when I try combining UPPER() with the contains (%value%). E.g. %UPPER(value)%

where ... AND (UPPER(l.name1) LIKE %UPPER(:search1)% OR UPPER(l.name2) LIKE %UPPER(:search2)% OR UPPER(l.name3) LIKE %UPPER(:search3)%

search1, search2, and search3 are parameters being passed in from my service implementation. Is there a different way of getting this logic of contains ignore case in the query? Or am I missing something small? I've tried finding examples but couldn't find any that relate directly to this situation.

Thank you in advance for the help!

Caleb
  • 15
  • 7

1 Answers1

1

First, you are not supposed to wrap the SQL function (UPPER) with % but the literal value.

In any case the can overcome your problem using CONCAT function:

...AND (UPPER(l.name1) LIKE CONCAT('%', CONCAT(UPPER(:search1), '%'))...

Also, note that you have an extra left parenthesis. You will probably have to add an extra ) just after LIKE %UPPER(:search3)%

Inspired by Parameter in like clause JPQL

Community
  • 1
  • 1
Ori Dar
  • 18,687
  • 5
  • 58
  • 72