mysql函式substring_index實現split切割效果

liftsail發表於2024-05-31
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

相關文章