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

mysql, select, sql

Solution

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

- SQLFiddle Demo

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)

- SQLFiddle Demo

Problem

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 | +--------+ ```

Original source