List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:October 17 2003 12:03am
Subject:Re: natural sorting
View as plain text  
* Michael Winston
> At 2:06 PM -0500 10/16/2003, Dan Nelson wrote:
> >In the last episode (Oct 16), Michael Winston said:
> >>  Is there any way to do "natural sorting" in MySQL?  That is:
> >>  Chapter 1
> >>  Chapter 2
> >>  Chapter 10
> >>
> >>  instead of
> >>
> >>  Chapter 1
> >>  Chapter 10
> >>  Chapter 2
> >>
> >>  If not, are there any plans for this feature in future releases?
> >>  Would be pretty helpful...
> >
> >You can easily enough write a UDF that zero-pads all numbers in a
> >string to N digits, which would let you do "SELECT * FROM mytable ORDER
> >BY dwim_sort(textfield,5)"
>
> "Easily enough"?  ha ha.  I've looked at the example UDF and realized
> that I am in over my head on that one.  Still, I wasn't even aware of
> UDFs, so thanks.
>
> Anyone done this already or know of a good repository of UDFs?
> Google didn't provide anything useful...

I don't know of any UDF repository, just wanted to mention the option to
cast a substring to an integer and sort on that. It's not real natural
sorting, but it works for your example data, maybe you can use it:

SELECT chapter
  FROM tab1
  ORDER BY MID(chapter,8)+0

--
Roger

Thread
natural sortingMichael Winston16 Oct
  • Re: natural sortingDan Nelson16 Oct
    • Re: natural sortingMichael Winston17 Oct
      • Re: natural sortingRoger Baklund17 Oct
        • Help in queryingdelz17 Oct
          • RE: Help in queryingRob17 Oct
  • Re: natural sortingTobias Asplund16 Oct