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.
2
Answers
You can do this by using some regexes to extract the parts that you need and sort them:
This also solves potential issues with more alphabetical characters, like AB123
fills the left side with 0 to maximum 5 letters
for eg: B10 becomes 00B10 and A1 becomes 000A1
and then sorts them.