What's the equivalent of mysql RLIKE operator in Hibernate Query?

Question

I have the following query which runs perfectly in mysql.

SELECT * FROM Orders as o, Products as p  where o.productinfo RLIKE p.code;

Here I am joining two tables Orders and Products with RLIKE.

I am trying to implement the same in Hibernate.

Query query = session.createQuery("FROM Orders as o, Products as p  where o.productinfo RLIKE p.code");
List<Object[]> results = query.getResultList();

When I used RLIKE, the following error is thrown in run time.

{"errormessage":"org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: RLIKE 

I tried to implement the same with LIKE query and matched it with '%p.code%'.

Query query = session.createQuery("FROM Orders as o, Products as p  where o.productinfo LIKE '%p.code%'");

But it matches with the string "p.code" rather then the value.

What is the equivalent of RLIKE in HQL? Is there a different way to join two tables with LIKE in HQL?

Thanks.

Answer by @YCF_L: For any one trying to join two tables with like operator in Hibernate (mysql) can do it the following way.

SELECT * FROM Orders as o, Products as p  where o.productinfo LIKE CONCAT('%',p.code,'%');

Answer 1

What's the equivalent of mysql RLIKE operator in Hibernate Query?

RLIKE is the synonym for REGEXP so you can implement it in hibernate using REGEXP_LIKE, you can take a look about this here : How to search in multiple columns using one like operator in HQL (hibernate sql)

---

I tried to implement the same with LIKE query and matched it with '%p.code%'.

..., Products as p  where o.productinfo LIKE '%p.code%'");

But it matches with the string "p.code" rather then the value.

This is true, because you don't pass the correct value of p.code, you pass it like a String, instead you have two ways :

Query query = session.createQuery("....Products as p  where o.productinfo LIKE '%:code%'");
//------------------------------------------------------------------------------^^^^^^^
query.setParameter("code", p.code);

Or you can concatenate your code with your Query, but the first solution is better.

Query query = session.createQuery("....  where o.productinfo LIKE '%" + p.code + "%'");

---

EDIT

You can use like with CONCAT without specify the '' like this :

SELECT * FROM Orders as o, Products as p  where o.productinfo LIKE CONCAT('%',p.code,'%');

Answer 2

You can check regular expression way something like this way: Restrictions.sqlRestriction(word REGEXP '^[A-Z][A-Za-z]*$')

Please check the link which will be helpful for this case: Regular expression with criteria