mysql custom sorting first alpha then numeric using case when

Question

How can you sort a query using ORDER BY CASE WHEN REGEXP? or other alternatives? I don't want to use UNION. Thank you

mysql> SELECT `floor_id`, `floor_number` FROM `floors`;
+----------+--------------+
| floor_id | floor_number |
+----------+--------------+
|        1 | 4            |
|        2 | 7            |
|        3 | G            |
|        4 | 19           |
|        5 | B            |
|        6 | 3            |
|        7 | A            |
+----------+--------------+


Expected result:
+----------+--------------+
| floor_id | floor_number |
+----------+--------------+
|        7 | A            |
|        5 | B            |
|        3 | G            |
|        6 | 3            |
|        1 | 4            |
|        2 | 7            |
|        4 | 19           |
+----------+--------------+

Answer 1

Your use of REGEXP is pretty close. This should work:

SELECT floor_id, floor_number FROM floors ORDER BY CASE
WHEN floor_number REGEXP '[a-zA-Z]' THEN 0
ELSE 0+floor_number END ASC, floor_number;

The CASE statement ranks the lettered floors as 0 and uses 0+ to coerce the numeric floors into a number value that can be ordered. A second level sort by floor_number is then required so that the lettered floors order correctly as A,B,G. Without the 2nd level order the lettered floors would all be considered equivalent with a value of 0 and would not appear in a defined order.