List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:November 12 2001 9:20pm
Subject:Re: Natural Sort
View as plain text  
In the last episode (Nov 12), Michael Kedl said:
> Dan Nelson wrote:
> > In the last episode (Nov 12), Michael Kedl said:
> > > > Looking thru the MYSQL archives I see a few people had interest
> > > > in a "natural sort" method to sort text fields containing
> > > > numbers.  I to would like this feature.  Has anything been done
> > > > for this?
> > > >
> > > > Currently sorts text like:
> > > > 1200 - A tale of 3 dogs
> > > > 3 bright lights go on
> > > >
> > > > "Should be":
> > > > 3 bright lights go on
> > > > 1200 - A tale of 3 dogs
> >
> > Try "... ORDER BY textfield+0", which will force mysql to convert the
> > field to a number before sorting.
> 
> The number could be anywhere in the field.
> Life Science 10: Frogs
> Life Science 2: Apples

So you really want to sort on the first number in the text field?  Or
do you want to sort alpha until you see a number, in which case you
sort by number, then continue sorting alpha?  Like how would you want
the following sorted?

3 bright lights go on
1200 - A tale of 3 dogs
1200 - A tale of 06 dogs
1200 - A tale of 6 cats

You'll probably have to write your own sort function in C and link it
into mysql as a user-defined function (UDF).  The easiest way I can
think of is to expand and zero-fill any numbers you see, so numsort()
would return:

000003 bright lights go on
001200 - A tale of 000003 dogs
001200 - A tale of 000006 dogs
001200 - A tale of 000006 cats

and you can do a simple "ORDER BY numsort(field)" to get your 'natual
sort'.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Natural SortMichael Kedl12 Nov
  • Re: Natural SortDan Nelson12 Nov
Re: Natural SortDan Nelson12 Nov