Figure out some way to identify the parts you need so that you can extract them. Otherwise, you’re out of luck.
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))
->FROM housewares2;
+------------+------------------------------------------------------+ | id | LEFT(SUBSTRING(id,4),CHAR_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, and then stripping
off the rightmost suffix:
mysql>SELECT id, SUBSTRING(id,4), SUBSTRING(id,4,CHAR_LENGTH(id)-5)
->FROM housewares2;
+------------+-----------------+-----------------------------------+ | 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 housewares2
->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 housewares2
->ORDER BY SUBSTRING(id,4,CHAR_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 strips off
trailing nonnumeric suffixes and provides 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 is 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 nondigits). 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 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,
and 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, and 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 the expressions 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.
3.128.198.59