MySQL Cookbook, 2nd Edition

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

Figure out some way to identify the parts you need so that you can extract them. Otherwise, you’re out of luck.

Discussion

If the substrings that you want to use for sorting vary in length, you need a reliable means of extracting just the part of the column values that you want. To see how this works, create a housewares2 table that is like the housewares table used in Recipe 7.14, except that it has no leading zeros in the serial number part of the id values:

mysql>SELECT * FROM housewares2;
+------------+------------------+
| 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 over the first three characters. Then, of the remainder beginning 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))
    ->