Natural sorting technique in mysql

Question

I have having trouble performing natural sort in mysql. I am a mysql noob and maybe this is adding to the problem

I have a list of buses numbers at a website I work on

List was at first random

bus_name

F1
F22
F10
F15
F7
F90

I attempted to do a natural sort using this

mysql> SELECT version
-> FROM version_sorting
-> ORDER BY CAST(version AS UNSIGNED), version;

taken from http://www.mpopp.net/2006/06/sorting-of-numeric-values-mixed-with-alphanumeric-values/

However I have the following now

F1
F10
F15
F22
F7
F90

As I note I believe the issue above has something to do with an imaginary zero that mysql attaches with each string , thereby making F7 as F70 which is > F22

I would want my list to be like

F1
F7
F10
F15
F22
F90

Can you people help me here please

Saharsh Shah · Answer 1 · 2013-12-16T06:11:13.717

2

Try this:

SELECT vs.version
FROM version_sorting vs
ORDER BY CAST(SUBSTRING(vs.version,2) AS SIGNED), vs.version;

edited Dec 16 '13 at 06:11

answered Dec 16 '13 at 06:05

Saharsh Shah

28,687
8
48
83

score 1 · Accepted Answer · answered Dec 16 '13 at 06:01

1

could you try this?

SELECT version
FROM version_sorting
ORDER BY LENGTH(version),  version;

answered Dec 16 '13 at 06:01

Jason Heo

9,956
2
36
64

Nice use of Length I never thought of that. – Tin Tran Dec 16 '13 at 06:43
@TinTran in this case, both mine and Saharsh's query work correctly. But some situation, I guess only `LENGTH(..)` would work like http://stackoverflow.com/questions/153633/natural-sort-in-mysql – Jason Heo Dec 16 '13 at 06:49
Perfect. Worked without alteration . Thank you so much – Muhammad Ahmed AbuTalib Dec 16 '13 at 07:01

Natural sorting technique in mysql

2 Answers2