Mega Code Archive
To use the substrings for sorting, use the appropriate expressions in the ORDER BY clause
<![CDATA[
mysql> CREATE TABLE housewares3
-> (
-> id VARCHAR(20),
-> description VARCHAR(255)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO housewares3 (id,description)
-> VALUES
-> ('13-478-92-2', 'dining table'),
-> ('873-48-649-63', 'garbage disposal'),
-> ('8-4-2-1', 'microwave oven'),
-> ('97-681-37-66', 'bedside lamp'),
-> ('27-48-534-2', 'shower stall'),
-> ('5764-56-89-72', 'lavatory')
-> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM housewares3;
+---------------+------------------+
| id | description |
+---------------+------------------+
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 8-4-2-1 | microwave oven |
| 97-681-37-66 | bedside lamp |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
+---------------+------------------+
6 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM housewares3
-> ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1);
+---------------+------------------+
| id | description |
+---------------+------------------+
| 8-4-2-1 | microwave oven |
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
| 97-681-37-66 | bedside lamp |
+---------------+------------------+
6 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT * FROM housewares3
-> ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1)+0;
+---------------+------------------+
| id | description |
+---------------+------------------+
| 8-4-2-1 | microwave oven |
| 873-48-649-63 | garbage disposal |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
| 13-478-92-2 | dining table |
| 97-681-37-66 | bedside lamp |
+---------------+------------------+
6 rows in set (0.00 sec)
mysql>
mysql> drop table housewares3;
Query OK, 0 rows affected (0.00 sec)
mysql>
]]>