How should I use COLLATE in Hibernate Query

Question

I'am trying to use COLLATE statement in a Hibernate SQL query but, it doesn't recognize the statement.

CONSULTA: FROM Articulos WHERE activo=0 
              and (codigodearticulo like '%CIN EMB%' COLLATE='Modern_Spanish_CI_AI'
              or descripcion like '%CIN EMB%' COLLATE='Modern_Spanish_CI_AI'  
              or descripcionadicional like '%CIN EMB%' COLLATE='Modern_Spanish_CI_AI' )
              and codigodelinea in
              (select CODIGODELINEA from Lineas where CATAUTOPARTES='1')

And when app compile, Hibernate return this exception :

- line 1:107: unexpected token: COLLATE
- line 1:107: unexpected token: COLLATE
- line 1:107: unexpected token: COLLATE
- Error hibernate: unexpected token: COLLATE near line 1, column 107

I can't find the problem, in MSSQL Server it works fine.

Answer 1

Unfortunately HQL isn't a complete replacement for SQL, and doesn't seem to allow specifying Collation at all.

It can be specified on a Criteria query. See this answer