How do you sort fields which have numbers in a mathematical order

Question

The following users are present in our database..

create table users (id, name);

name [u1, u2, u3, u4, u5, u6, u7, u8, u9, u10, u11, ...]

If the above field is sorted in an ascending fashion, the output is as follows..

u1, u10, u11, u2, u3, u4 ...

Is it possible to sort this in the order at the top (i.e. list all single digits, double digits ...)

uel - http://stackoverflow.com/questions/153633/natural-sort-in-mysql — ajreal, Nov 12 '10 at 08:40

score 2 · Accepted Answer · answered Nov 12 '10 at 08:11

2

Try:

SELECT *
FROM users
ORDER BY CAST(SUBSTR(name FROM 2) AS UNSIGNED)

answered Nov 12 '10 at 08:11

eumiro

score 2 · Answer 2 · answered Nov 12 '10 at 08:12

2

You could try this:

SELECT name
FROM users
ORDER BY LENGTH(name), name

answered Nov 12 '10 at 08:12

Flinsch

2 Answers2