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.

Answer 1

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.