Sorting Mysql results based on a modified column

I have a mysql query which outputs 6 columns of related data, I'm specifically interested in having the results sorted alphabetically by a certain modified column. The unmodified results from that column look like this:

... | 0001: Some text here        |...
... | 0002: Flipped text here     |...
... | 0003: About some more text  |...

The '0001:' portion should not be displayed. Currently, I have a Perl subroutine which removes that portion from being displayed, but I don't know how I can get all rows sorted alphabetically based off of that resulting column. What I'm looking for would be:

... | About some more text  |...
... | Flipped text here     |...
... | Some text here        |...

The following is what I'm using to retrieve and display said data, but my understanding of Perl is sorely lacking. I don't understand how the @$data works, just know that it does. My failed sorting attempt is commented out by the #.

$data = $sth->fetchall_arrayref();
$sth->finish;

foreach $data ( @$data) {
    ($a, $name, $c, $d, $e, $f) = @$data;
    # @$data = 开发者_开发技巧sort { "\L$a->out_name([2])" cmp "\L$b->out_name([2])"} @$data;
    $Response->Write($a.",".out_name($name).",".$c.",".$d.",".$e.",".$f."<br />");
}

Any help or ideas would be greatly appreciated, thank you.

EDIT: I failed to note, the '0001:' portion may appear as '511:' or '85000:', that number isn't a constant length. If there's a way to deal with this condition in Mysql, that would be excellent. It didn't seem like a possibility in my searches, thus why I was attempting it with Perl.

You can get the database to do the sorting for you:

SELECT ...
FROM yourtable
ORDER BY SUBSTR(yourcolumn, 5)

Here is an explanation of @$data related stuff. The DBI method

$data = $sth->fetchall_arrayref();

returns reference to an array of rows, where each is reference to array of columns. The foreach uses dereference to traverse first arrayref (I am using more descriptive variable names)

foreach $row_ref (@$data) {

Then each row is decomposed to individual columns using list assignment and dereference

($a, $name, $c, $d, $e, $f) = @$row_ref;

Your sort attempt failed because you tried to sort each row individually. It should rather look like this

foreach my $row_ref (sort { out_name($a->[1]) cmp out_name($b->[1]) } @$data) {
     ....
}

Also consider sorting on database query level suggested by Mark Byers, it might be better approach.

Having the database sort for you will probably be faster, but if you want to/have to do it in perl, something like this should work:

@sorted_data = sort {
    my ($x) = ($a->[1] =~ m/^\d+: (.*)/); 
    my ($y) = ($b->[1] =~ m/^\d+: (.*)/);
    $x cmp $y;
} @$data;

foreach (@sorted_data) { print output }

[1] is the position of the column of interest in the @$data array, hardcoding the index is not ideal.

@$data (the first one) contains all the results from your query, and you should be sorting it outside your foreach block.

@sorted_data = sort { 
    my($x=$a) =~ s/^\d+: //; 
    my($y=$b) =~ s/^\d+: //;
    $x cmp $y
} @$data;
foreach my $data (@sorted_data) { 
   ... 
}    

(Reusing $data and @$data the way you did is syntactically valid but hella confusing)

My first proposision is to hold this column in two different columns. To join them is always easier than separate them dynamically.