Clever Geek Handbook

Sort numbers After letters in ORDER BY

Hi i am trying to order in mysql by sorting

  • Special symbols
  • Letters
  • then Numbers

it looks like mysql sorts by

  • Special symbols
  • 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, I will be forever grateful.

All line codes always contain 3 characters.

+3


source to share


1 answer


Something like this would work if it was always the second character - you could add to the case argument 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 seems 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 also Fiddle .

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

Good luck.

+5


source






More articles:


All Articles