HQL String LEFT

Question

How do I get records that first 4 characters' match my string using HQL?

This is my HQL statement:

Query query = HibernateUtil.getSessionFactory().getCurrentSession().createQuery("from User where substring(user, 4) like :user");

query.setParameter("user", "John");

Which returns nothing. However, if I reuse the SQL statement in MySQL and change the word "substring" to LEFT then it returns a lot of records.

I've also tried replacing "substring" with "left" in my HQL but it gives me this error when I do:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: LEFT

in HQL your not allowed to use mysql functions. So, better you go with native sql. i.e. : https://stackoverflow.com/questions/8648787/how-to-implement-mysql-date-sub-function-in-hibernate-3-0 — murtaza.webdev, Jan 21 '14 at 13:41

score 2 · Accepted Answer · answered Jan 21 '14 at 13:45

The equivalent of left requires substring() with three arguments. The second is the offset and the third is the number of characters. So, to get the first four characters:

Query query = HibernateUtil.getSessionFactory().getCurrentSession().createQuery("from User where substring(user, 1, 4) like :user");

The expression substring(user, 4) usually takes the substring starting at the fourth character to the end of the string (I say "usually" because that is typical behavior of the function but I don't know if HQL exactly does that).

score 1 · Answer 2 · answered Jan 21 '14 at 13:44

1

Can't you do this with a like and wildcards instead?

Query query = HibernateUtil.getSessionFactory().getCurrentSession().createQuery(
"from User u where u.user like :user");

query.setString("user", "John%");

Otherwise do a native query

answered Jan 21 '14 at 13:44

Einar Bjerve

524
2
12

HQL String LEFT

2 Answers2