Sort using combinations of substrings
mysql>
mysql> CREATE TABLE housewares
-> (
-> id VARCHAR(20),
-> description VARCHAR(255)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO housewares (id,description)
-> VALUES
-> ('DIN40672US', 'dining table'),
-> ('KIT00372UK', 'garbage disposal'),
-> ('KIT01729JP', 'microwave oven'),
-> ('BED00038SG', 'bedside lamp'),
-> ('BTH00485US', 'shower stall'),
-> ('BTH00415JP', 'lavatory')
-> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM housewares;
+------------+------------------+
| id | description |
+------------+------------------+
| DIN40672US | dining table |
| KIT00372UK | garbage disposal |
| KIT01729JP | microwave oven |
| BED00038SG | bedside lamp |
| BTH00485US | shower stall |
| BTH00415JP | lavatory |
+------------+------------------+
6 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM housewares ORDER BY RIGHT(id,2), MID(id,4,5);
+------------+------------------+
| id | description |
+------------+------------------+
| BTH00415JP | lavatory |
| KIT01729JP | microwave oven |
| BED00038SG | bedside lamp |
| KIT00372UK | garbage disposal |
| BTH00485US | shower stall |
| DIN40672US | dining table |
+------------+------------------+
6 rows in set (0.00 sec)
mysql>
mysql> drop table housewares;
Query OK, 0 rows affected (0.00 sec)
Related examples in the same category