Can MySQL sort by "Natural Order" ?

[Anon]

Can MySQL sort like the natsort(); function of PHP4? Maybe something like: "Select * from table order by column NATURAL", that probably won't work, but is there any function for MySQL that does do this? Or must I go through writing my own function or modifying each row to have a 0 in front of each number 🙁.

[[deleted]]

I trust you don't just have one number in a string type column?

[Anon]

Yes I also have text in the column. It's like "Blah blah 1", then mysql would sort "Blah blah 10" next, which is why I want natural order sorting.

[Anon]

Can't anyone help me?

[Anon]

not sure, but a hack would be to set the results in an array and do the sort that way. i did not look at the MYSQL.com home page, but it may be a good start.

[Anon]

I had a similar problem:

I am using several values, joined with hyphens, to serve as a key, which I later split into the three separate values. I too was getting the keys coming out in the unnatural order.

I solved the problem like this:

If any of the individual numbers is less than 10, I add a '0' to the number on INSERT. (eg. assignmentKey = '01-02-10'). When the keys are listed, they list in the proper order.

Then when I split the values back out, I usePHP's settype() function to convert the numerical strings to their integer values, thus dropping the '0's.

list($dbLangNum,$dbModNum,$dbActNum) = split ("-", $activityKey);
settype($dbLangNum, "integer");
settype($dbModNum, "integer");
settype($dbActNum, "integer");

So, what I get for each is:

$dbLangNum = 1;
$dbModNum = 2;
$dbActNum = 10;

A bit of extra overhead, but it works nicely.

Good luck.

Ken