Question 234938 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – how to order by '0057','0058', '1', '2', '3', 'A1', 'A2', 'A3' in sql

how to order by ‘0057’,’0058′, ‘1’, ‘2’, ‘3’, ‘A1’, ‘A2’, ‘A3’ in sql

Can anyone plese give idea to diplay the section data (alpha numeric column) in this below order?

Actual

0057
0058
10
11
5
6
A5
A6

Expected

0057
0058
5
6
10
11
A5
A6

I tried this statement
a.section REGEXP '^-?[0-9]+$' DESC, length(a.section + 0), a.section this response is like this 1, 2, 0057, 0058, 10, 12, A1, A2. but we expect output like this 0057, 0058,1, 2, 10, 12, A1, A2

Tags:

2

Answers


  1. 0 
    SQL query   = "SELECT column_name
FROM your_table
ORDER BY
    CASE
        WHEN column_name ~ E'^\d+$' THEN LPAD(column_name, 20, '0') -- Numeric part (padded with zeros)
        ELSE column_name -- Alphanumeric part
    END;"
    Login or Signup to reply.
  2. 0

    You say:

    • First order: strings that start with ‘0’.
    • Second-order: integer values.
    • Finally: alphanumeric (A1, A2, B5).

    If that’s the case, you can do:

    select col
from (
  select t.col,
    case when col like '0%' then 1
         when col regexp '^[0-9]+$' then 2
         else 3 end as grp
  from t
) x
order by grp, col
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search