CODEWITHSUNDEEP
phpmysql
Elitmiar
Elitmiar

Reputation: 36839

using Order By when string consist of numbers and letters to sort only by numbers

I have a column were I need to sort by that contains Alphabetical characters and numbers

Now the following values

ABC223 ABC12

Now I need to sort this only by numbers ignoring the Alphabetical characters in the string,

Something to remember is sometimes the value can start with GD, othertimes with only A sometimes 123AB et

Is this at all possible?

Upvotes: 0

Views: 212

Answers (3)

Leri
Leri

Reputation: 12535

You can use uasort to sort array of containing that values.

For example:

$pattern = "/\d+/";
$values = array('ABC123', 'ABC12', 'GD44'); //Just for example

uasort($values, function ($a, $b) use ($pattern)
   {
       $matches = array();
       preg_match($pattern, $a, $matches);
       $a = $matches[0];
       preg_match($pattern, $b, $matches);
       $b = $matches[0];

       return $a - $b;
   });

var_dump($values);

Also you can use udf for manipulating regex using sql query.

Upvotes: 0

jaco
jaco

Reputation: 3516

Take a look at: https://launchpad.net/mysql-udf-regexp and How to do a regular expression replace in MySQL?

If you use udf you can execute an ORDER BY with a regex replace.

Upvotes: 1

Daniel A. White
Daniel A. White

Reputation: 190943

You could do something like this:

ORDER BY SUBSTRING(YourColumn, 3)

Upvotes: 2

Related Questions