Ordering by attribute that contains letters and numbers

Question

We need to order a list of room numbers. You can think of the units being numerically ordered, with the possibility of a letter in front as a prefix, e.g 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, A1, A2, A3, A4, A5, A6, A7, A8, A9, A10, B1, B2, B3, etc.

After reading MySQL 'Order By' - sorting alphanumeric correctly, here's what I tried:

create table units (id integer, unit_number varchar(100));

insert into units (id, unit_number) values (1, 'A1');
insert into units (id, unit_number) values (2, 'A2');
insert into units (id, unit_number) values (3, 'A3');
insert into units (id, unit_number) values (4, 'A4');
insert into units (id, unit_number) values (5, 'A5');
insert into units (id, unit_number) values (6, 'A6');
insert into units (id, unit_number) values (7, 'A7');
insert into units (id, unit_number) values (8, 'A8');
insert into units (id, unit_number) values (9, 'A9');
insert into units (id, unit_number) values (10, 'A10');
insert into units (id, unit_number) values (11, 'B1');
insert into units (id, unit_number) values (12, 'B2');
insert into units (id, unit_number) values (13, 'B3');
insert into units (id, unit_number) values (14, 'B4');
insert into units (id, unit_number) values (15, 'B5');
insert into units (id, unit_number) values (16, 'B6');
insert into units (id, unit_number) values (17, 'B7');
insert into units (id, unit_number) values (18, 'B8');
insert into units (id, unit_number) values (19, 'B9');
insert into units (id, unit_number) values (20, 'B10');

select * from units ORDER BY LENGTH(unit_number), unit_number;

When I get the results back, I'll get ordering like this:

| id       | unit_number    |
| -------- | -------------- |
| 1        | A1             |
| 2        | A2             |
| 3        | A3             |
| 4        | A4             |
| 5        | A5             |
| 6        | A6             |
| 7        | A7             |
| 8        | A8             |
| 9        | A9             |
| 10       | B1             |
| 11       | B2             |
| 12       | B3             |
| 13       | B4             |
| 14       | B5             |
| 15       | B6             |
| 16       | B7             |
| 17       | B8             |
| 18       | B9             |
| 19       | A10            |
| 20       | B10            |

How can I rewrite this query so that the ordering places A10 after A9? This is more of the expectation from the user's perspective.

Answer 1

You can do this by using some regexes to extract the parts that you need and sort them:

SELECT unit_number
FROM  units
ORDER BY
    SUBSTRING(unit_number FROM '^[A-Za-z]+'), -- This sorts the alphabetical part.
    CAST(SUBSTRING(unit_number FROM '[0-9]+$') AS INTEGER); -- This sorts the numerical part.

This also solves potential issues with more alphabetical characters, like AB123