Subject Re: [Firebird-Java] JPA (Hibernate) and SUBSTRING
Author Mark Rotteveel
tdtappe wrote:
> I would like to (I have to) use SUBSTRING in a JPA-Query. But it's obviously not translated into the correct Firebird syntax using SUBSTRING(value FROM start FOR length).
> Is there anything special I need to do for that? Some configuration?

The Interbase and Firebird dialects in Hibernate are rather limited and
include none (or: almost none) of the Firebird functions. A while back I
did start on an updated dialect. I haven't finished it because I had no
use for Hibernate at that time.

I will see if I can finish that dialect and post it online. For now the
easiest is to extend the existing FirebirdDialect and add the following
in your constructor (after the call to super()):
registerFunction("substring", new SQLFunctionTemplate(Hibernate.STRING,
"substring(?1 from ?2 for ?3"));

Then instruct Hibernate to use your own dialect.

This function probably does not cover all substring variants though.

Mark Rotteveel