Hello,

I am trying to convert a hexadecimal string to a number for a where clause in HQL. I tried:

"from Table tb where cast(tb.hexstringcolumn as int) between 100 and 1000" .......

This works if tb.hexstringcolumn is a numeric string (e.g. 10). It will not work if tb.hexstringcolumn = FF. Any thoughts?

First post, so please be gentle (I read the before you post and searched the forum)

Easy way: convert your hex string into a standard Java int or long before binding it into your query.

Hard way: write a custom UserType object to bind it in to the queries yourself.

I don't want to bind it at all. I am using the where clause to include a range of hex strings. Once this is done, it doesn't matter to me whether the hex string is a string or int. I found that I can use hex() to convert the string to a numeric representation. This is not the best method, but seems to be working. What would be better is if 'cast (column as int)' would recognize integer and hexadecimal strings.

What your are doing is what I meant by "easy way". cast() is a database operation, you'll have to talk to your DB vendor about that (although you might find if you dig through your DB vendor docs that it might do what you want if you prefix "0x" or somesuch).