CODEWITHSUNDEEP
mysql
Danial Khayatian
Danial Khayatian

Reputation: 13

how to order my string column using substr and cast in mysql

my column id data is like :

99999/1
99999/2
99999/5
99999/10
99999/8
99999/4
....

when i use

order by cast(SUBSTR(id,7) as unsigned)

my data order likes:

999999/4
999999/3
999999/5
999999/6
999999/1
999999/7
999999/8
999999/9
999999/10
999999/11
999999/12
999999/13
999999/14
999999/15
999999/16
999999/17
999999/18
999999/19

how do I can order this column id by second part numbers exist after '/' character

Upvotes: 0

Views: 107

Answers (1)

ysth
ysth

Reputation: 98398

To order it by the first part and then the second part:

order by cast(substring_index(id,'/',1) as unsigned),cast(substring_index(id,'/',-1) as unsigned)

To order only by the second part, just:

order by cast(substring_index(id,'/',-1) as unsigned)

Upvotes: 1

Related Questions