PHP String Contains Integer

Question

I've the following strings as follows :

1BG200,1M400,1BA1000

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

Code:

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

Output :

1M400
1BG200
1BA1000

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 !

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

score 3 · Accepted Answer · answered Jun 24 '13 at 12:37

3

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;
    return ($a < $b) ? -1 : 1;
}

//Now get the list and sort
usort($list, "cmp");

answered Jun 24 '13 at 12:37

Otto

879
9
7

2

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

score 2 · Answer 2 · answered Jun 24 '13 at 12:31

2

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.

answered Jun 24 '13 at 12:31

jcubic

61,973
54
229
402

score 1 · Answer 3 · edited May 23 '17 at 12:14

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.

score 0 · Answer 4 · answered Jun 24 '13 at 12:43

0

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.

answered Jun 24 '13 at 12:43

flec

2,891
1
22
30

score 0 · Answer 5 · answered Aug 14 '13 at 13:44

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

digitstri='1M400'; // just an example
number=intval(is_number(digitstri{2})?substr(digitstri,2):substr(digitstri,3))

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

PHP String Contains Integer

5 Answers5