Mega Code Archive
Sorting by Variable-Length Substrings
<![CDATA[
mysql>
mysql> CREATE TABLE housewares2
-> (
-> id VARCHAR(20),
-> description VARCHAR(255)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO housewares2 (id,description)
-> VALUES
-> ('DIN40672US', 'dining table'),
-> ('KIT372UK', 'garbage disposal'),
-> ('KIT1729JP', 'microwave oven'),
-> ('BED38SG', 'bedside lamp'),
-> ('BTH485US', 'shower stall'),
-> ('BTH415JP', 'lavatory')
-> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM housewares2;
+------------+------------------+
| id | description |
+------------+------------------+
| DIN40672US | dining table |
| KIT372UK | garbage disposal |
| KIT1729JP | microwave oven |
| BED38SG | bedside lamp |
| BTH485US | shower stall |
| BTH415JP | lavatory |
+------------+------------------+
6 rows in set (0.00 sec)
mysql>
mysql> SELECT id, SUBSTRING(id,4) FROM housewares2;
+------------+-----------------+
| id | SUBSTRING(id,4) |
+------------+-----------------+
| DIN40672US | 40672US |
| KIT372UK | 372UK |
| KIT1729JP | 1729JP |
| BED38SG | 38SG |
| BTH485US | 485US |
| BTH415JP | 415JP |
+------------+-----------------+
6 rows in set (0.00 sec)
mysql>
]]>