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

Question

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?

If all your strings start with "ABC" it should do that already... — jacoz, Oct 24 '12 at 13:10
Forgot to mention, sometimes it can start with GD, othertimes with only A sometimes 123AB etc — Elitmiar, Oct 24 '12 at 13:13

score 2 · Answer 1 · answered Oct 24 '12 at 13:10

2

You could do something like this:

ORDER BY SUBSTRING(YourColumn, 3)

answered Oct 24 '12 at 13:10

Daniel A. White

187,200
47
362
445

Thank you but I forgot to mention sometimes it can start with GD, othertimes with only A sometimes 123AB etc so SUBSTRING wont work in this case – Elitmiar Oct 24 '12 at 13:14

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

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.

edited May 23 '17 at 10:34

Community

1
1

answered Oct 24 '12 at 13:20

jacoz

3,508
5
26
42

Leri · Answer 3 · 2012-10-24T13:29:25.513

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.

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

3 Answers3