MySQL Cookbook

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 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 the previous section, 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 and return the remainder beginning with the fourth character (the first digit):

mysql> SELECT id, SUBSTRING(id,4) FROM housewares2; +------------+-----------------+ | id | SUBSTRING(id,4) | +------------+-----------------+ ...