开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜