Database Reference
In-Depth Information
7.8. Sorting by Variable-Length Substrings
Problem
You want to sort using parts of a column that do
not
occur at a given position within
the column.
Solution
Determine how to identify the parts you need so that you can extract them. Otherwise,
you're out of luck.
Discussion
If substrings to be used for sorting vary in length, you need a reliable means of extracting
just the part you want. To see how this works, create a
housewares3
table that is like the
housewares
table used in
Recipe 7.7
, except that it has no leading zeros in the serial
number part of the
id
values:
mysql>
SELECT * FROM housewares3;
+------------+------------------+
| id | description |
+------------+------------------+
| DIN40672US | dining table |
| KIT372UK | garbage disposal |
| KIT1729JP | microwave oven |
| BED38SG | bedside lamp |
| BTH485US | shower stall |
| BTH415JP | lavatory |
+------------+------------------+
The category and country parts of the
id
values can be extracted and sorted using
LEFT()
and
RIGHT()
, just as for the
housewares
table. But now the numeric segments of the
values have different lengths and cannot be extracted and sorted using a simple
MID()
call. Instead, use
SUBSTRING()
to skip the first three characters. Of the remainder be‐
ginning with the fourth character (the first digit), take everything but the rightmost two
columns. One way to do this is as follows:
mysql>
SELECT id, LEFT(SUBSTRING(id,4),CHAR_LENGTH(SUBSTRING(id,4)-2))
->
FROM housewares3;
+------------+------------------------------------------------------+
| id | LEFT(SUBSTRING(id,4),CHAR_LENGTH(SUBSTRING(id,4)-2)) |
+------------+------------------------------------------------------+
| DIN40672US | 40672 |
| KIT372UK | 372 |
| KIT1729JP | 1729 |
| BED38SG | 38 |
| BTH485US | 485 |
