mysql函数substring_index实现split切割效果
SELECT
SUBSTRING_INDEX( SUBSTRING_INDEX( '12,2,3,4,5,6,7,8,9,6', ',', id ), ',',- 1 ) AS num,
id
FROM
sequence_id
WHERE
id <= LENGTH(
REPLACE ( '1,2,3,4,5,6,7,8,9,10', ',', '' ));
DROP TABLE IF EXISTS `sequence_id`;
CREATE TABLE `sequence_id` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序列id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of sequence_id
-- ----------------------------
INSERT INTO `sequence_id` VALUES (1);
INSERT INTO `sequence_id` VALUES (2);
INSERT INTO `sequence_id` VALUES (3);
INSERT INTO `sequence_id` VALUES (4);
INSERT INTO `sequence_id` VALUES (5);
INSERT INTO `sequence_id` VALUES (6);
INSERT INTO `sequence_id` VALUES (7);
INSERT INTO `sequence_id` VALUES (8);
MySQL中一个很好用的截取字符串的函数:substring_index。
用法规则:
substring_index(“待截取有用部分的字符串”,“截取数据依据的字符”,截取字符的位置N)
详细说明:
首先,设待处理对象字符串为“15,151,152,16”(虽然这里指的不是iP,可以看作是IP来处理吧)
这里截取的依据是逗号:“,”
具体要截取第N个逗号前部分的字符;
意思是:在字符串中以逗号为索引,获取不同索引位的字符。
举例如下:
1. 取第一个逗号前的字符串 :
root@localhost|iris>SELECT SUBSTRING_INDEX('15,151,152,16',',',1);
+----------------------------------------+
| SUBSTRING_INDEX('15,151,152,16',',',1) |
+----------------------------------------+
| 15 |
+----------------------------------------+
1 row in set (0.00 sec)
==>得到结果为: 15
2.截取第二个逗号前面部分
root@localhost|iris>SELECT SUBSTRING_INDEX('15,151,152,16',',',2);
+----------------------------------------+
| SUBSTRING_INDEX('15,151,152,16',',',2) |
+----------------------------------------+
| 15,151 |
+----------------------------------------+
1 row in set (0.00 sec)
==>得到结果为:15,151
N可以为负数,表示倒数第N个索引字符后面的字符串。有负号的时候,可以将整个字符倒过来看,依旧是第N个字符前面的部分。
1.截取目标字符串中最后一个含 “,” 位子的后的部分:
root@localhost|iris>SELECT SUBSTRING_INDEX('15,151,152,16',',',-1) as 'subrting';
+----------+
| subrting |
+----------+
| 16 |
+----------+
1 row in set (0.00 sec)
==>得到结果为: 16
2.取倒数第2个逗号前那部分字符串里,最后逗号后面的部分
root@localhost|iris>SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16',',',2),',',-1) as 'subrting';
+----------+
| subrting |
+----------+
| 151 |
+----------+
1 row in set (0.00 sec)
==>得到结果为: 151
3.取倒数第二个逗号后面部分字符串,再去这部分里第一个都号前的部分:
root@localhost|iris>SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16',',',-2),',',1) as 'subrting';
+----------+
| subrting |
+----------+
| 152 |
+----------+
1 row in set (0.00 sec)
==> 得到结果为:152