Mega Code Archive
Get the numeric middle part by beginning with the ID, then stripping off the rightmost suffix
<![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> SELECT id, SUBSTRING(id,4), SUBSTRING(id,4,LENGTH(id)-5)
-> FROM housewares2;
+------------+-----------------+------------------------------+
| id | SUBSTRING(id,4) | SUBSTRING(id,4,LENGTH(id)-5) |
+------------+-----------------+------------------------------+
| DIN40672US | 40672US | 40672 |
| KIT372UK | 372UK | 372 |
| KIT1729JP | 1729JP | 1729 |
| BED38SG | 38SG | 38 |
| BTH485US | 485US | 485 |
| BTH415JP | 415JP | 415 |
+------------+-----------------+------------------------------+
6 rows in set (0.00 sec)
mysql>
mysql> drop table housewares2;
Query OK, 0 rows affected (0.00 sec)
]]>