【MySQL】substring_index 函数详解

朱雀 2024-03-25 12:36 70阅读 0赞

![在这里插入图片描述][ff684cfe207441b39b45d9f868c3e85f.png_pic_center]

## 【MySQL】substring\_index 函数详解 ##

### **命令格式** ###

string substring_index(string <str>, string <separator>, int <count>)

### **命令说明** ###

截取字符串**str**第**count**个分隔符之前的字符串。如果**count**为正，则从左边开始截取。如果**count**为负，则从右边开始截取。此函数为MaxCompute 2.0扩展函数。

### **参数说明** ###

*  **str**：必填。STRING类型。待截取的字符串。
 *  **separator**：必填。STRING类型的分隔符。
 *  **count**：必填。INT类型。指定分隔符位置。

### **返回值说明** ###

返回`STRING`类型。如果任一输入参数值为NULL，返回NULL。

### **栗子1** ###

*  示例1：截取字符串`https://help.codingce.com`。命令示例如下：

# 返回 https://help.codingce
    select substring_index('https://help.codingce.com', '.', 2);
    # 返回 codingce.com
    select substring_index('https://help.codingce.com', '.', -2);

*  示例2：任一输入参数为NULL。命令示例如下：

# 返回NULL
    select substring_index('https://help.codingce.com', null, 2);

### **栗子2** ###

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

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for tb_ip
    -- ----------------------------
    DROP TABLE IF EXISTS `tb_ip`;
    CREATE TABLE `tb_ip`  (
      `pid` int(11) NOT NULL AUTO_INCREMENT,
      `ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`pid`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of tb_ip
    -- ----------------------------
    INSERT INTO `tb_ip` VALUES (1, '127.0.0.1');
    INSERT INTO `tb_ip` VALUES (2, '192.128.0.15');
    INSERT INTO `tb_ip` VALUES (3, '255.255.255.255');
    
    SET FOREIGN_KEY_CHECKS = 1;

SELECT
    	ip,
    	SUBSTRING_INDEX( tb_ip.ip, '.', 1 ) AS part1,
    	SUBSTRING_INDEX( SUBSTRING_INDEX( tb_ip.ip, '.', 2 ), '.', -1) AS part2,
    	SUBSTRING_INDEX( SUBSTRING_INDEX( tb_ip.ip, '.', 3 ), '.', -1) AS part3,
    	SUBSTRING_INDEX( SUBSTRING_INDEX( tb_ip.ip, '.', 4 ), '.', -1) AS part4
    FROM
    	tb_ip;

结果：

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

### **栗子3** ###

可以说是一个面试题，解法有多种，那么如果用 `SUBSTRING_INDEX` 要如何编写呢？

![9adde35cc24b692304678b0b2afde909.png][]

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for tb_value_output
    -- ----------------------------
    DROP TABLE IF EXISTS `tb_value_output`;
    CREATE TABLE `tb_value_output`  (
      `pid` int(11) NOT NULL AUTO_INCREMENT,
      `year` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `month` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `amount` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`pid`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of tb_value_output
    -- ----------------------------
    INSERT INTO `tb_value_output` VALUES (1, '1991', '1', '1.1');
    INSERT INTO `tb_value_output` VALUES (2, '1991', '2', '1.2');
    INSERT INTO `tb_value_output` VALUES (3, '1991', '3', '2.2');
    INSERT INTO `tb_value_output` VALUES (4, '1991', '4', '2.5');
    INSERT INTO `tb_value_output` VALUES (5, '1992', '1', '2.1');
    INSERT INTO `tb_value_output` VALUES (6, '1992', '2', '2.4');
    INSERT INTO `tb_value_output` VALUES (7, '1992', '3', '2.1');
    INSERT INTO `tb_value_output` VALUES (8, '1992', '4', '2.5');
    
    SET FOREIGN_KEY_CHECKS = 1;

SELECT
    	vo.year,
    	SUBSTRING_INDEX( GROUP_CONCAT( vo.amount ), ",", 1 ) AS m1,
    	SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT( vo.amount ), ",", 2 ), ",", - 1 ) AS m2,
    	SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT( vo.amount ), ",", - 2 ), ",", 1 ) AS m3,
    	SUBSTRING_INDEX( GROUP_CONCAT( vo.amount ), ",", - 1 ) AS m4 
    FROM
    	tb_value_output vo 
    GROUP BY
    	vo.year

结果：

year	m1	m2	m3	m4
    1991	1.1	1.2	2.2	2.5
    1992	2.1	2.4	2.1	2.5

### 其它 ###

GROUP\_CONCAT 函数：将相同的行组合起来。

[ff684cfe207441b39b45d9f868c3e85f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/433216539acb4eb2bc99a829bad7f363.png
[9adde35cc24b692304678b0b2afde909.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/f615678de7d34aea8b32596aa275be67.png