Travis Travis - 3 months ago 5
MySQL Question

How can I sort A1,A2,A3,B1,B2,B3,...,AA1,AA2,AA3 in MySQL?

I have a data set that looks like this:

+--------+
| Square |
+--------+
| A1 |
| A10 |
| A2 |
| A3 |
| A4 |
| A5 |
| A6 |
| A7 |
| A8 |
| A9 |
| B1 |
| B10 |
| B2 |
| B3 |
| B4 |
| B5 |
| B6 |
| B7 |
| B8 |
| B9 |


...

| AA1 |
| AA10 |
| AA2 |
| AA3 |
| AA4 |
| AA5 |
| AA6 |
| AA7 |
| AA8 |
| AA9 |
+--------+


The prefix runs from A#-Z#, then goes AA#-ZZ# and continues, with a maximum of 2 letters (i.e. it will never go over ZZ). The number suffix can be any length (i.e. A1,A10,A100,A1000,etc.).

How can I sort these and have the result set come out as follows:

+--------+
| Square |
+--------+
| A1 |
| A2 |
| A3 |
| A4 |
| A5 |
| A6 |
| A7 |
| A8 |
| A9 |
| A10 |
| B1 |
| B2 |
| B3 |
| B4 |
| B5 |
| B6 |
| B7 |
| B8 |
| B9 |
| B10 |


...

| AA1 |
| AA2 |
| AA3 |
| AA4 |
| AA5 |
| AA6 |
| AA7 |
| AA8 |
| AA9 |
| AA10 |
+--------+

Answer

You need to restructure your table or else you will likely have inefficient query like this,

SELECT  Square
FROM    Table1
ORDER   BY         
        CASE WHEN Square REGEXP '^[A-Z]{2}'
            THEN 1 
            ELSE 0
        END ASC,
        CASE WHEN Square REGEXP '^[A-Z]{2}'
            THEN LEFT(Square, 2)
            ELSE LEFT(Square, 1)
        END ASC,
        CASE WHEN Square REGEXP '^[A-Z]{2}'
            THEN CAST(RIGHT(Square, LENGTH(Square) - 2) AS SIGNED)
            ELSE CAST(RIGHT(Square, LENGTH(Square) - 1) AS SIGNED)
        END ASC

Or by using IF

SELECT  Square
FROM    Table1
ORDER   BY Square REGEXP '^[A-Z]{2}' ASC,
           IF(Square REGEXP '^[A-Z]{2}', LEFT(Square, 2), LEFT(Square, 1)),
           CAST(IF(Square REGEXP '^[A-Z]{2}', RIGHT(Square, LENGTH(Square) - 2), RIGHT(Square, LENGTH(Square) - 1)) AS SIGNED)
Comments