how to use string left function in hql

Question

I have a sql query like this

select column from table where path = left('INPUTSTRING', length(path));

and trying to accomplish it in hql like this,

  return session.createQuery("from Table where Path = left(:input, length(Path))").
                            query.setParameter("input", inputPath).
                            .list();

and getting an error like this

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: left near line 1

how to get this done? What is the corresponding string function in hql? Is there a solution for this using criteria query apis?

Answer 1

Yes, left() is not supported by the MySQLDialect. See the list of HQL supported functions on API docs.

Now you have left with 2 options.

1. Use session.createSQLQuery() method.
2. Create Your own Dialect class by extending the MySQLDialect and register the function there. This is told at hibernate forum here explained well in a blog post here.