converting mysql query to hibernate hql

Question

There is an Issue while i try to auto Increment alphanumeric Id (or number Series) for example

in Mysql "Sample" Table 'RefNo' column (of type Varchar)

AB7 AB10 AB9 AB8

above i have four entries now i want to retrieve max (or highest value).

for this i have tried query as = SELECT MAX(RefNo) FROM sample; but this gives result as 'AB9' which is wrong and it is supposed to return 'AB10' as result.

To get this in Mysql i have modified the Query as = SELECT MAX(CONVERT(SUBSTRING_INDEX(RefNo,'B',-1),UNSIGNED INTEGER)) from sample where RefNo like 'AB%'

this work's fine in mysql but in hibernate (hql) query is not supported.

I hope you understand the scenerio and Please help me to solve the issue.

JB Nizet · Accepted Answer · 2012-09-25 12:57:29Z

2

You should be able to use substring and cast to get the numeric part of the string and cast it to a number. Something like that (not tested) :

select max(cast(substring(sample.refNo, 3) as INTEGER)) from Sample sample ...

answered Sep 25, 2012 at 12:57

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

converting mysql query to hibernate hql

in Mysql "Sample" Table 'RefNo' column (of type Varchar)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

in Mysql "Sample" Table 'RefNo' column (of type Varchar)

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related