CODEWITHSUNDEEP
mysqlhibernate
viquar
viquar

Reputation: 37

converting mysql query to hibernate hql

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.

Upvotes: 0

Views: 1325

Answers (1)

JB Nizet
JB Nizet

Reputation: 692181

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 ...

Upvotes: 2

Related Questions