JPQL and Upper case queries in LIKE

I had a situation where I needed to write a query which would perform upper case comparisons in a LIKE statement. It took me a while to figure this out, mainly because I didn't realize the right-side of the LIKE statement does not support any of the functions. So given a query like this:

  SELECT name FROM Countries WHERE name LIKE '%someValue%';

to write this in a valid JPQL format (assuming we have a persistent bean Country with an attribute name, you would write it out as follows:

  String searchExp = "find me";
  Query qs = entity.createQuery("SELECT c.name FROM Country c WHERE UPPER(c.name) LIKE '%:val%'");
  qs.setParameter("val",searchExp.toUpperCase());

The key here is to convert the right-side in code and then you can use the function UPPER( ) for the left-side of the expression.

This was tested with MySQL and HSQLDB with EclipseLink. There was an article I found here which states that with MySQL 5.1 onward you don't need to use the LOWER function, but I have not verified this yet (nor found the collaborating evidence on MySQL's website). Running against my test system in HSQLDB (in memory) it did not find the results in my unit tests.