Database Reference
In-Depth Information
| BTH415JP | 415 |
+------------+------------------------------------------------------+
But that's more complex than necessary. The
SUBSTRING()
function takes an optional
third argument specifying a desired result length, and we know that the length of the
middle part is equal to the length of the string minus five (three for the characters at the
beginning and two for the characters at the end). The following query demonstrates
how to get the numeric middle part by beginning with the ID, and then stripping the
rightmost suffix:
mysql>
SELECT id, SUBSTRING(id,4), SUBSTRING(id,4,CHAR_LENGTH(id)-5)
->
FROM housewares3;
+------------+-----------------+-----------------------------------+
| id | SUBSTRING(id,4) | SUBSTRING(id,4,CHAR_LENGTH(id)-5) |
+------------+-----------------+-----------------------------------+
| DIN40672US | 40672US | 40672 |
| KIT372UK | 372UK | 372 |
| KIT1729JP | 1729JP | 1729 |
| BED38SG | 38SG | 38 |
| BTH485US | 485US | 485 |
| BTH415JP | 415JP | 415 |
+------------+-----------------+-----------------------------------+
Unfortunately, although the final expression correctly extracts the numeric part from
the IDs, the resulting values are strings. Consequently, they sort lexically rather than
numerically:
mysql>
SELECT * FROM housewares3
->
ORDER BY SUBSTRING(id,4,CHAR_LENGTH(id)-5);
+------------+------------------+
| id | description |
+------------+------------------+
| KIT1729JP | microwave oven |
| KIT372UK | garbage disposal |
| BED38SG | bedside lamp |
| DIN40672US | dining table |
| BTH415JP | lavatory |
| BTH485US | shower stall |
+------------+------------------+
How to deal with that? One way is to add zero, which tells MySQL to perform a string-
to-number conversion that results in a numeric sort of the serial number values:
mysql>
SELECT * FROM housewares3
->
ORDER BY SUBSTRING(id,4,CHAR_LENGTH(id)-5)+0;
+------------+------------------+
| id | description |
+------------+------------------+
| BED38SG | bedside lamp |
| KIT372UK | garbage disposal |
| BTH415JP | lavatory |
| BTH485US | shower stall |
