Hi,

I want to use both Oracle 'to_char' and MSSQLServer 'convert' functions in HQL queries.

Is there any supported classes in Hibernate-3.1 version? Please tell me if anyone knows about it.

Thanks in Advance,
LALITH

Hello I'm looking for this solution too.

Please, let me know how to convert any data type to string for comparison proposal.

I would like to implement an universal text search function in all field of a table. How can I do it using HQL ?

I tried to use the following:

1) (Works) from Category where lower(id)='8'

2) (does not work) from Category where lower(id)='%8%'

3) (does not work)
Criteria criteria = session.createCriteria(Category.class);
criteria.add(Expression. ge("id", "8"));

How to convert id to string and then compare it with something ?

Thanks !

Jocafi

Hi Lalith,

I think I found a solution for to_char(). Use the HQL function str(), lower(), upper().

It worked for me:

from Category where lower(str(id)) like '%8%'

And I corrected the setence 2 in my last post and it worked:

2) from Category where lower(id) like '%8%'

I didn't tested it for other data types like date, boolean, float, timestamp, blob and so on.

For the problem using convert() I read somewhere that you can use it:

cast(... as ...), where the second argument is the name of a Hibernate type, and extract(... from...) if ANSI cast() and extract() is supported by the underlying database

Please, don't forget to rate this post !

Good Luck !

Jocafi