substring from the right

[Frans]

Hi,

I'm using Querydsl 3.2.2. with Hibernate 4.2.4 and MySQL and I face a problem trying to extract portion of a string field starting from the right.

Typically, I'm trying to find all accounts whose name have identical characters at position x and y (starting from the right).

In MySQL, I could write it as follows (as negative values make it start counting from the right):

SELECT * FROM account where SUBSTRING(name, -x, 1) = SUBSTRING(name, -y, 1)

I tried two options to transpose that into Querydsl (using JPA queries):

• 1) substring

QAccount account = ...;
account.name.substring(account.name.length().subtract(x + 1), account.name.length().subtract(x)).eq(account.name.length().subtract(y + 1), account.name.length().subtract(y))

But this gives the following error:

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: Not all named parameters have been set: [4] [select a.name
from Account a
where substring(a.name,length(a.name) - ?2,length(a.name) - ?2-length(a.name) - ?1) = substring(a.name,length(a.name) - ?4,length(a.name) - ?3-length(a.name) - ?3)]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1374)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:277)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:311)
    at com.sun.proxy.$Proxy172.getResultList(Unknown Source)
    at com.mysema.query.jpa.impl.AbstractJPAQuery.getResultList(AbstractJPAQuery.java:196)
    at com.mysema.query.jpa.impl.AbstractJPAQuery.list(AbstractJPAQuery.java:242)
    ... 74 more
Caused by: org.hibernate.QueryException: Not all named parameters have been set: [4] [select a.name
from Account a
where substring(a.name,length(a.name) - ?2,length(a.name) - ?2-length(a.name) - ?1) = substring(a.name,length(a.name) - ?4,length(a.name) - ?3-length(a.name) - ?3)]
    at org.hibernate.internal.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:344)
    at org.hibernate.internal.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:328)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:97)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:268)
    ... 88 more
   

• 2) charAt

I discovered you can pass negative values for the StringExpression.chartAt() method, so I tried as follows:

QAccount account = ...;
account.name.charAt(-x)).eq(account.name.charAt(-y))

But it seems x & y values are transformed as follows (to adapt to the 1-based indexes of MySQL):
if >= 0 : increment by 1
if < 0 : decrement by 1

Then, the question is: how do I select the last character, i.e. -1 at MySQL level? If I pass -1, it converts to -2, and if I pass 0, it converts to 1.

Do I miss something?

Thanks a lot for your help.

Frans

[Timo Westkämper]

Hi.

This seems to be a missing feature. Could you create an issue for it on GitHub?

Br,
Timo

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[Frans]

Hi Timo,

There it is: https://github.com/mysema/querydsl/issues/475

I'm just surprised the substring option does not work as it seems standard function use (while negative indexes is indeed more unusual).

Thank you for the great tool, I love it!

Frans

On Thursday, August 15, 2013 3:24:21 PM UTC+2, Timo Westkämper wrote:

Hi.

This seems to be a missing feature. Could you create an issue for it on GitHub?

Br,
Timo