mysql natural sort?

dreams4000

Hi all,

Does mysql have some kind of natural sort function like php's natsort() function? I have some data lists with numbers and I want the numbers to sort out like 1, 2,3,4....10 instead of 1,10,2,3,30,4,5 etc.. any ideas?

rachel2004

You may have to post the query that you're currently using as well as table structure and sample records. Otherwise, using the ORDER BY clause should do what you're asking for in most cases.

dreams4000

There is nothing special or unique about the setup.. its simply a field.. say Names.. That field is being populated by a form where folks can enter a name.. sometimes the name will have numbers in front of it, other times it will have just words. But in the times that it has numbers.. I want them to naturally order, .. so if I had..

1.0 - Dogs
2.0 - Cats
3.0 -Whatever
.
.
.
.
9.0 -something else

the 10.0 is going under the 1.0 to look like

1.0 - Dogs
10.0 - Lots of stuff
2.0 - Cats
3.0 -wahtever

It should natually sort so the 10.0 comes after the 9.0 item.. I already have done a simple order by on the colum and that does the 1.0,10.0 problem. php has a natural sort function to do something like that, but I need something similar in mysql if possible. Any thoughts? Thanks!

rachel2004

I saw your other post asking the same question. I'm going to have to say that IF YOU CAN, break this into two columns as drawmack suggested. If not all of your values will have names or not all of them will have numbers, you have to weigh how often that will occur and live with some empty values for those cells. Sure, we want to be perfectionists with our databases, but sometimes we make judgement calls and live an acceptable level of imperfection.

If you can't, I can't see another way to sort them in a manner which would seem natural to us humans.