I would go look this up for you normally but I can't find the documentation on hibernate right now.
I think HQL might have a mod operator and I think this is what you actually want to use. I can't verify it it really exists though.
The mod operator returns the remainder so 5 mod 2 = 1 and 3 mod 2 = 1
So you would look for anything (s.age mod 10)=:age to find any ages that end in a certain number.
Most databases that I know of have some form of the mod operator for instance sql server
If it does exist then you would be able to do something like this
SELECT s FROM Student s WHERE mod(s.age,10)=:age
I suspect that this would be faster than a string comparison and conversion however, I am not sure either.
Actually I think i found some documentation half way down this page
Any function or operator defined by EJB-QL 3.0: substring(), trim(), lower(), upper(), length(), locate(), abs(), sqrt(), bit_length(), mod()
There is always more than one way to do things but hopefully this helps you.
For ethnarch: First of all, excuse my late answer. Also thanks for spending your time trying to help me.
I think that I should have chosen a more generic example, since that one of numbers ending in five was only to get an approximation to my problem in real terms. As you stated it could be solved using a mathematic function even with more efficiency.
My real need is to know if there's a way to compare a column of type numeric against a text string, using the LIKE comparator without tricks like the one which I mentioned above.
Let's think about a comparison which can't be solved using mathematic functions (at least, of my knowledge). let's suppose that I need to get every price which at least has the sequence 23 in it, such as 2394.34, 388231.00, 323.10, 0.235, etc.
I would use this JPQL query:
SELECT p FROM Products p WHERE p.price LIKE '%23%'
However this would cause an exception if I used an input parameter instead of typing that expression literally, since EJB needs that both, the parameter and the column, must be of the same type:
SELECT p FROM Products p WHERE p.price LIKE ?
Where ? is the String %23% passed as parameter. This causes an error.
So I would need to use the TRIM trick to avoid the exception:
SELECT p FROM Products p WHERE TRIM (p.price) LIKE ?
Doing that change I could even pass just the string % as parameter to choose every record, without having to alter the query dynamically (a true problem when they're static, inserted into every entity, as my personal case in this project).
I'm frightened that this trick can't be used in the future, when a new release of JBoss forces the TRIM argument to be a String and can't be a number, as the Sun documentation states.
Thanks for your pointer to CONCAT.
I also have the same need but that works with JPA 2.0 and Hibernate 5.0.2:
SELECT p FROM MatchProfile p WHERE CONCAT(p.id, '') = :keyword
Works wonders. I think it works with LIKE too.