Mysql 常用函数（42）- substring_index 函数

Mysql常用函数的汇总，可看下面系列文章

https://www.cnblogs.com/poloyy/category/1765164.html

 

作用

按照指定的分隔符划分字符串，可指定分隔符出现的次数

 

substring_index  的语法格式

SUBSTRING_INDEX(str, delim, count)

语法格式说明

• str：需要操作的字符串
• delim：分隔符
• count：匹配 delim 出现的次数，可正数可负数

 

栗子一

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

• 如果是负数则从右边开始寻找 delim，正数则是左边
• 如果是1、-1，则返回第一个 delim 之前的字符串
• 如果>1、< -1，则还会返回前面匹配到的 delim

 

栗子二

假设有三个 IP：127.0.0.1、192.128.0.15、255.255.255.255，要分别取每一个号段的值并返回

 

sql 语句

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 要如何编写呢？

 

sql 语句

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

 

执行结果