mysql sort varchar with numbers
How can I sort this data numerically rather than lexicographically?
100_10A
100_10B
100_10C
100_11A
100_11B
100_11C
100_12A
100_12B
100_12C
100_13A
100_13B
100_13C
100_14A
100_14B
100_14C
100_15A
100_15B
100_15C
100_16A
100_16B
100_16C
100_1A
100_1B
100_1C
100_2A
100_2B
100_2C
100_3A
100_3B
100_3C
100_4A
100_4B
100_4C
100_5A
100_5B
100_5C
100_6A
100_6B
100_6C
100_7A
100_7B
100_7C
100_8A
100_8B
100_8C
100_9A
100_9B
100_9C
select generalcolum from mytable order by blockid, plotid AS开发者_StackOverflow中文版C
What I need out of this sort order is
100_1A
100_1B
100_1C...
...
...
100_10A
100_10B
100_10C
What I need to do in some way is have a zero added before the sort happens so that, I can get them in the order I want.
There are two colums, one that stores the 100 (number before the underscore) and one that stores the 1A the value after the underscore.
My sudo crap select
select thiscolum this table
order by blockid, plotid(+1 zero to prefix if len(plotid) < 2)
For example if the plot value is 1A, to do the best sorting, i need it to be looked at as 01A so that it comes before 10A.
order by length(blockid), blockid, length(plotid), plotid
精彩评论