On Wed, Sep 25, 2002 at 04:17:57PM -0400, John Almberg wrote:
> I'm trying to sort a SQL table that contains a character-type field that
> contains mostly numbers. This field always contains either a number or a
> number followed by a character. Like '57' or '57a'.
>
> I'd like to sort the table *numerically* on this field, not *alphabetically*
> on this field. That is, I'd like the table to be sorted like:
>
> 1 ...
> 2a ...
> 3 ...
> 4d ...
Do you want to sort the number along with the letter? For example, should
2a come before 2b, or does it matter?
If all you want is to sort on just the numeric portion:
sub by_numeric {
my($a_num) = ($a =~ /^(\d+)/);
my($b_num) = ($b =~ /^(\d+)/);
return $a_num <=> $b_num;
}
sort by_numeric @data;
If you want to sort by both:
sub by_numeric_then_alpha {
my($a_num, $a_alpha) = ($a =~ /^(\d+)(\D*)/);;
my($b_num, $b_alpha) = ($b =~ /^(\d+)(\D*)/);
return $a_num <=> $b_num || $a_alpha cmp $b_alpha;
}
sort by_numeric_then_alpha @data;
Optimizations on things such as code length or results caching I will leave
to the reader. Incidentally, cases like these are mentioned in perldoc -f
sort.
> A pseudo-code solution would be SELECT * FROM table ORDER BY
> INT(char-field), but unfortunately even MySql doesn't have such an INT()
> cast funtion.
>
> I'm asking this perl group, because I suspect that my only solution is to
> sort the result set in perl. Am I right about this???
>From my brief review of mysql's string functions it would appear you will
only be able to do this with an external language.
Michael
--
Administrator www.shoebox.net
Programmer, System Administrator www.gallanttech.com
--
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
