Database Reference
In-Depth Information
By combining
SUBSTRING_INDEX()
calls with positive and negative indexes, it's possible
to extract successive pieces from each
id
value: extract the first
n
segments of the value
and 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_IN
DEX()
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
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_IN
DEX()
call is sufficient to return the final substring:
SUBSTRING_INDEX(id,'-',-1)
These expressions can be difficult to read and understand, and experimenting with a
few to see how they work may be useful. 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 housewares4;
+---------------+----------+----------+
| 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 a