mysql natural sort

I have example number in the format 开发者_StackOverflow:

1.1
1.1.4
1.1.5
2.1
2.1.10
2.1.23
3.1a
3.1b
4.1.5
4.2.6
4.7.12

How do I sort it in MySQL ? I can do that easily from the $sort command line option but nothing seems to work in MySQL

It may work if you split the string into pieces and order by each relevant piece.

SELECT data
FROM example
ORDER BY
    CAST(SUBSTRING_INDEX(data, '.', 1) AS BINARY) ASC,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data , '.', 2), '.', -1) AS BINARY) ASC,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data , '.', -1), '.', 1) AS BINARY) ASC;

Can't say I support doing something like that in MySQL, but I guess it would get you where you need to be, at least with my test data. Just remember you'll need to edit the number if you change the number of elements in the string.

Try ordering by INET_ATON (for MySQL 3.23.15 and newer)

ORDER BY INET_ATON(some_field);

PS. It works for IP addresses, don't know how it handle letters

Was there something wrong with ORDER BY?

I tried:

CREATE TABLE example (data VARCHAR(30));
INSERT INTO example VALUES ('4.2.6'), ('1.1.5'), ('2.1.10'), ('3.1b'), ('2.1'), ('4.7.12'), ('1.1'), ('2.1.23'), ('1.1.4'), ('3.1a'), ('4.1.5');
SELECT * FROM example ORDER BY data;

... and it seemed to work as you'd like. (I can't guarantee that there isn't some corner case where your real data might not ordered by what you'd consider "natural." That seems to be a heuristic term rather than a precisely defined term of art.