Take the 2-minute tour ×

I've the following strings as follows :


And I want to to compare the above strings into Desc Order...


$sql = "SELECT * FROM collected WHERE c_no BETWEEN '".$from."' AND '".$to."' ORDER BY c_no Desc";

Output :


It should be 1000 is larger, then 400, 200..How can i compare them ? I believe its not right to compare string that contains integer ! And I can't find a correct solution for my issue ?

Some people suggested using preg_match or substr..But as you can see there are single and double characters ex ( M and BG ).

Sorry, I'm not that familiar with PHP.. Please Help !

share|improve this question
So, the only dependent part for the sorting algorithm is the ending digit sequence? –  Daniel Jun 24 '13 at 12:29
@Daniel Well I wanted to compare the full string, but it seems that its not working with me. So yea, i want to ignore the letters and sort digit sequence. –  Alihamra Jun 24 '13 at 12:32
add comment

5 Answers

up vote 2 down vote accepted

You can use a custom sort, looking only at the numerical part

function cmp($a, $b)
    $numa = intval(preg_replace('/[0-9]*[A-Z]+/', '', $a));
    $numb = intval(preg_replace('/[0-9]*[A-Z]+/', '', $b));
    if($a == $b) return 0;

//Now get the list and sort
usort($list, "cmp");
share|improve this answer
In addition if you'd want the leading digit to be part of the sorting algorithm as well you'd exchange the pattern for "/[^0-9]/" which will base the sorting on all the digits. –  Daniel Jun 24 '13 at 12:42
add comment

You can use preg_replace('/[0-9][A-Z]+/', '', $var) to remove first number and more then one letter after, and then use php usort.

share|improve this answer
add comment

You could add a custom function to your MySQL. Found one that looks like it could strip out all the non digit characters MySQL strip non-numeric characters to compare.

I would highly recommend doing this over bringing everything back to php and sorting if you ever decide to use limit / offset due to large results being returned. Otherwise you would have to pull everything back to PHP then splice an array at which point I feel it would be an inefficient use of resources.

Alternatively, you could add a sort column to your table if that is a feasable option, to allow you to better utilize indexes in MySQL which depending on your record set may be a huge performance difference.

share|improve this answer
+1 for the sorting column –  Gerald Schneider Jun 24 '13 at 12:49
add comment

You could add the user defined preg-functions to your mysql (https://github.com/mysqludf/lib_mysqludf_preg). Then use PREG_CAPTURE in your order by clause.

share|improve this answer
add comment

in general preg is expensive. To gain a number from the given pattern I would do something like this:

digitstri='1M400'; // just an example

I think, the sorting from there is clear...

share|improve this answer
add comment

Your Answer


