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
Search WWH ::




Custom Search