按照指定的分隔符划分字符串,可指定分隔符出现的次数
SUBSTRING_INDEX(str, delim, count)
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1); # www
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -1); # com
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); # www.mysql
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); # mysql.com
假设有三个 IP:127.0.0.1、192.128.0.15、255.255.255.255,要分别取每一个号段的值并返回
SELECT ip,
SUBSTRING_INDEX(ip,'.',1) AS part1,
SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',2),'.',-1) AS part2,
SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-2),'.',1) AS part3,
SUBSTRING_INDEX(ip,'.',-1) AS part4
FROM IPS;
+-----------------+-------+-------+-------+-------+
| ip | part1 | part2 | part3 | part4 |
+-----------------+-------+-------+-------+-------+
| 127.0.0.1 | 127 | 0 | 0 | 1 |
| 192.128.0.15 | 192 | 128 | 0 | 15 |
| 255.255.255.255 | 255 | 255 | 255 | 255 |
+-----------------+-------+-------+-------+-------+
中间的号段需要重复调用 SUBSTRING_INDEX,因为第一次调用的时候还是会返回两个号段
可以说是一个面试题,解法有多种,那么如果用 SUBSTRING_INDEX 要如何编写呢?
SELECT YEAR,
SUBSTRING_INDEX( GROUP_CONCAT( amount ), ",", 1 ) AS m1,
SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT( amount ), ",", 2 ),",", -1 ) AS m2,
SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT( amount ), ",", - 2 ),",", 1 ) AS m3,
SUBSTRING_INDEX( GROUP_CONCAT( amount ), ",", - 1 ) AS m4
FROM
tests
GROUP BY
YEAR