Sort numbers After letters in ORDER BY

Question

Hello i am trying to order by in mysql by sorting - Special characters - Letters - then Numbers it seems like mysql sorts by - Special Characters - Numbers - then Letters See below ``` select distinct uag_linecode from part order by uag_linecode; | A-1 | | A/C | | A1S | out of place | ABP | | ABS | | ACI | | ADM | | ADR | | BAS | ``` This is what i want: ``` A-1 A/C ABR ABS ACI ADM ADR A1S correct spot BAS ``` If anyone can help me out i would be eternally grateful. All the line codes are always 3 characters long.

Accepted Answer

Something like this would work if it were always the 2nd character -- you might be able to add to the case clause to check for 1st and 3rd characters...

```
select distinct uag_linecode,
substring(uag_linecode, 2, 1)
from part
order by 
  case 
    when substring(uag_linecode, 2, 1) REGEXP ('^[0-9]') THEN 1 ELSE 0 END , uag_linecode
```

Here is the SQL Fiddle.

--EDIT This appears to work for 1st and 3rd characters as well:

```
select distinct uag_linecode,
   substring(uag_linecode, 1, 1),
   substring(uag_linecode, 2, 1),
   substring(uag_linecode, 3, 1)
from part
order by 
   case when substring(uag_linecode, 1, 1) REGEXP ('^[0-9]') THEN 1 ELSE 0 END,
   substring(uag_linecode, 1, 1),
   case when substring(uag_linecode, 2, 1) REGEXP ('^[0-9]') THEN 1 ELSE 0 END,
   substring(uag_linecode, 2, 1),
   case when substring(uag_linecode, 3, 1) REGEXP ('^[0-9]') THEN 1 ELSE 0 END ,    
   substring(uag_linecode, 3, 1)
```

And more Fiddle.

Also as @Orbling correctly says, you'll get a better performance (especially if you have lots of records) to remove the REDEXP and use >= '0' AND <= '9'.

Good luck.

Sort numbers After letters in ORDER BY

Solution

Problem

Related problems