Issue
I am using oracle 10g
and hibernate 3.3.2
. I have used regular expression in sql before, now for the first time I am using it in HQL.
Query query = getSession().createQuery("From Company company
where company.id!=:companyId and
regexp_like(upper(rtrim(ltrim(company.num))), '^0*514619915$' )");
This is my hql, when i run it without regex_like
function it runs as expected. But I am not able to execute it with regex_like
expression.
It says..
nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 66.....
Kindly help, how can I use regex_like
in hibernate native query? OR some other alternative to do so.
Solution
Actually, you can't compare the result of REGEXP_LIKE to anything except in conditional statements in PL/SQL.
Hibernate seems to not accept a custom function without a returnType, as you always need to compare the output to something, i.e:
REGEXP_LIKE('bananas', 'a', 'i') = 1
As Oracle doesn't allow you to compare this function's result to nothing, I came up with a solution using case condition:
public class Oracle10gExtendedDialect extends Oracle10gDialect {
public Oracle10gExtendedDialect() {
super();
registerFunction(
"regexp_like", new SQLFunctionTemplate(StandardBasicTypes.BOOLEAN,
"(case when (regexp_like(?1, ?2, ?3)) then 1 else 0 end)")
);
}
}
And your HQL should look like this:
REGEXP_LIKE('bananas', 'a', 'i') = 1
It will work :)
Answered By - Marcelo Liberato