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
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
