How to sort a column by HQL if the column has both string and integer values

Question

I have a column "event_number" in the "event" table. The "event_number" column is actually a varchar column in mysql.

I want to sort data according to "event_number" using HQL query. (Sleep mode).

For example, data

event_number
1
2
M-2
4
5
K-C
6
7
8
9
10
11
TY-C

The output should be:

event_number
1
2
4
5
6
7
8
9
10
11
K-C
M-2
TY-C

or The output should be:

event_number
K-C
M-2
TY-C
1
2
4
5
6
7
8
9
10
11

I used HQL query:

select 
    event.eventNumber 
from 
    event 
order by 
    event.eventNumber asc;

I got the result:

event_number
1
10
11
2
4
5
6
7
8
9
K-C
M-2
TY-C

+3

Ishwar lal Dec 12. 14 at 12:31

2 answers

You can try this

select cast(event.eventNumber as integer) from event order by event.eventNumber asc;

+1

Adarsh khalique · Accepted Answer · 2014-12-15T10:52:28+0000

This will work

select
    event.eventNumber
from
    event
order by
    cast(event.eventNumber as integer), event.eventNumber asc

"cast (event.eventNumber as integer)" sorts only integer values.

"cast (event.eventNumber as integer), event.eventNumber asc" sorts both integer and string values