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
– Daniel, Commented Jun 24, 2013 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
– Alihamra, Commented Jun 24, 2013 at 12:32

Otto · Accepted Answer · 2013-06-24 12:37:20Z

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, 2013 at 12:37

Otto

9479 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Over a year ago

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.

jcubic · Accepted Answer · 2013-06-24 12:31:31Z

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, 2013 at 12:31

jcubic

67.1k58 gold badges252 silver badges466 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:14:20Z

1

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.

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Jun 24, 2013 at 12:46

Joe Meyer

4,4452 gold badges23 silver badges28 bronze badges

Comments

flec · Accepted Answer · 2013-06-24 12:43:06Z

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, 2013 at 12:43

flec

3,0191 gold badge24 silver badges30 bronze badges

Comments

Quicker · Accepted Answer · 2013-08-14 13:44:17Z

0

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...

answered Aug 14, 2013 at 13:44

Quicker

1,2469 silver badges16 bronze badges

Collectives™ on Stack Overflow

PHP String Contains Integer

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related