Database Reference
In-Depth Information
| KIT1729JP | microwave oven |
| DIN40672US | dining table |
+------------+------------------+
In this particular case, a simpler solution is possible. It's unnecessary to calculate the
length of the numeric part of the string, because a string-to-number conversion oper‐
ation strips 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 housewares3
->
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 char‐
acters 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
housewares4
table with
id
values that look like this:
mysql>
SELECT * FROM housewares4;
+---------------+------------------+
| 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)
