Sorting by Variable-Length Substrings

6.14.1 Problem

You want to sort using parts of a column that do not occur at a given position within the column.

6.14.2 Solution

Figure out some way to identify the parts you need so you can extract them; otherwise, you're out of luck.

6.14.3 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) |
+------------+-----------------+
| DIN40672US | 40672US |
| KIT372UK | 372UK |
| KIT1729JP | 1729JP |
| BED38SG | 38SG |
| BTH485US | 485US |
| BTH415JP | 415JP |
+------------+-----------------+

Then take everything but the rightmost two columns. One way to do this is as follows:

mysql> SELECT id, LEFT(SUBSTRING(id,4),LENGTH(SUBSTRING(id,4)-2))
 -> FROM housewares2;
+------------+-------------------------------------------------+
| id | LEFT(SUBSTRING(id,4),LENGTH(SUBSTRING(id,4)-2)) |
+------------+-------------------------------------------------+
| DIN40672US | 40672 |
| KIT372UK | 372 |
| KIT1729JP | 1729 |
| BED38SG | 38 |
| BTH485US | 485 |
| 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, then stripping off the rightmost suffix:

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 |
+------------+-----------------+------------------------------+

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 housewares2
 -> ORDER BY SUBSTRING(id,4,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 housewares2
 -> ORDER BY SUBSTRING(id,4,LENGTH(id)-5)+0;
+------------+------------------+
| id | description |
+------------+------------------+
| BED38SG | bedside lamp |
| KIT372UK | garbage disposal |
| BTH415JP | lavatory |
| BTH485US | shower stall |
| KIT1729JP | microwave oven |
| DIN40672US | dining table |
+------------+------------------+

But in this particular case, a simpler solution is possible. It's not necessary to calculate the length of the numeric part of the string, because the string-to-number conversion operation will strip off trailing non-numeric suffixes and provide the values needed to sort on the variable-length serial number portion of the id values. That means the third argument to SUBSTRING( ) actually isn't needed:

mysql> SELECT * FROM housewares2
 -> ORDER BY SUBSTRING(id,4)+0;
+------------+------------------+
| id | description |
+------------+------------------+
| BED38SG | bedside lamp |
| KIT372UK | garbage disposal |
| BTH415JP | lavatory |
| BTH485US | shower stall |
| KIT1729JP | microwave oven |
| DIN40672US | dining table |
+------------+------------------+

In the preceding example, the ability to extract variable-length substrings was based on the different kinds of characters in the middle of the ID values, compared to the characters on the ends (that is, digits versus non-digits). In other cases, you may be able to use delimiter characters to pull apart column values. For the next examples, assume a housewares3 table with id values that look like this:

mysql> SELECT * FROM housewares3;
+---------------+------------------+
| id | description |
+---------------+------------------+
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 8-4-2-1 | microwave oven |
| 97-681-37-66 | bedside lamp |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
+---------------+------------------+

To extract segments from these values, use SUBSTRING_INDEX(str,c,n). It searches into a string str for the n-th occurrence of a given character c and returns everything to the left of that character. For example, the following call returns 13-478:

SUBSTRING_INDEX('13-478-92-2','-',2)

If n is negative, the search for c proceeds from the right and returns the rightmost string. This call returns 478-92-2:

SUBSTRING_INDEX('13-478-92-2','-',-3)

By combining SUBSTRING_INDEX( ) calls with positive and negative indexes, it's possible to extract successive pieces from each id value. One way is to extract the first n segments of the value, then pull off the rightmost one. By varying n from 1 to 4, we get the successive segments from left to right:

SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',1),'-',-1)
SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1)
SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',3),'-',-1)
SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',4),'-',-1)

The first of those expressions can be optimized, because the inner SUBSTRING_INDEX( ) call returns a single-segment string and is sufficient by itself to return the leftmost id segment:

SUBSTRING_INDEX(id,'-',1)

Another way to obtain substrings is to extract the rightmost n segments of the value, then pull off the first one. Here we vary n from -4 to -1:

SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',-4),'-',1)
SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',-3),'-',1)
SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',-2),'-',1)
SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',-1),'-',1)

Again, an optimization is possible. For the fourth expression, the inner SUBSTRING_INDEX( ) call is sufficient to return the final substring:

SUBSTRING_INDEX(id,'-',-1)

These expressions can be difficult to read and understand, and you probably should try experimenting with a few of them to see how they work. Here is an example that shows how to get the second and fourth segments from the id values:

mysql> SELECT
 -> id,
 -> SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1) AS segment2,
 -> SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',4),'-',-1) AS segment4
 -> FROM housewares3;
+---------------+----------+----------+
| id | segment2 | segment4 |
+---------------+----------+----------+
| 13-478-92-2 | 478 | 2 |
| 873-48-649-63 | 48 | 63 |
| 8-4-2-1 | 4 | 1 |
| 97-681-37-66 | 681 | 66 |
| 27-48-534-2 | 48 | 2 |
| 5764-56-89-72 | 56 | 72 |
+---------------+----------+----------+

To use the substrings for sorting, use the appropriate expressions in the ORDER BY clause. (Remember to force a string-to-number conversion by adding zero if you want the sort to be numeric rather than lexical.) The following two queries order the results based on the second id segment. The first sorts lexically, the second numerically:

mysql> SELECT * FROM housewares3
 -> ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1);
+---------------+------------------+
| id | description |
+---------------+------------------+
| 8-4-2-1 | microwave oven |
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
| 97-681-37-66 | bedside lamp |
+---------------+------------------+
mysql> SELECT * FROM housewares3
 -> ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1)+0;
+---------------+------------------+
| id | description |
+---------------+------------------+
| 8-4-2-1 | microwave oven |
| 873-48-649-63 | garbage disposal |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
| 13-478-92-2 | dining table |
| 97-681-37-66 | bedside lamp |
+---------------+------------------+

The substring-extraction expressions here are messy, but at least the column values to which we're applying them have a consistent number of segments. To sort values that have varying numbers of segments, the job can be more difficult. The next section shows an example illustrating why that is.