CODEWITHSUNDEEP
javasqlhibernatehqlcriteria
Labeeb Panampullan
Labeeb Panampullan

Reputation: 34833

Sort table by integer value stored in varchar field

Me working in spring hibernate
I have column in my database table, that of type VARCHAR, but it store integer value. So if i sort it using sql or hql or Criteria(Order.asc), all are sorting it as string. I need it to be sorted as integer value it store. Here i cannot alter my table.
Is There anyway to sort it as integer using Criteria
Is the only solution for me is, after reading it to some list and sort inside my service?

Edited : me using MYSQL

Thank you

Upvotes: 0

Views: 2567

Answers (2)

Mohamed Saligh
Mohamed Saligh

Reputation: 12349

SQL:

SELECT CAST(t.order AS UNSIGNED INTEGER) AS intOrder 
    FROM myTable t ORDER BY intOrder ASC

Upvotes: 0

Joel
Joel

Reputation: 30156

You could try casting it before sorting it. In mysql, something like this:

SELECT CAST(myVarcharField AS DECIMAL(10)) as myIntField order by myIntField;

or as an unsigned integer:

SELECT CAST(myVarcharField AS UNSIGNED) as myIntField order by myIntField;

cast appears to be a valid HQL expression too.

Upvotes: 1

Related Questions