【MySQL】substring_index 函数详解_java

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

【MySQL】substring_index 函数详解_ci_02

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 函数：将相同的行组合起来。