mysql按固定字符进行切割_MySQL:SQL按特定字符分隔切割或删除特定字符

1,先建表

DROP TABLE IF EXISTS `gongzi`;

CREATE TABLE `gongzi` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

`gw` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of gongzi

-- ----------------------------

INSERT INTO `gongzi` VALUES (1, '张三', '13k-23k');

INSERT INTO `gongzi` VALUES (2, '李四', '5k-8k');

INSERT INTO `gongzi` VALUES (3, '王五', '9k-12k');

SET FOREIGN_KEY_CHECKS = 1;

运行SELECT语句：

2,按特定字符分割列

按照中划线-分割列

函数：substring_index(字段,'特定字符',索引)

索引为1表示第一个，

索引为-1表示最后一个。

以下以-号将gw列分隔

substring_index(gw,'-',1)

具体SQL操作：

SELECT id, name, gw, substring_index(gw,'-',1) as low, substring_index(gw,'-',-1) as high FROM gongzi;

3,删除特定字符

函数：Replace(字段名,原字符, 新字符)

试想一下：将新字符写为空字符''，不就是删除了么

以下将字符K去掉

Replace(字段名,'k','')

具体SQL操作:

SELECT id, name, gw, substring_index(gw,'-',1) as low, substring_index(gw,'-',-1) as high, Replace(substring_index(gw,'-',1),'k','') as deleteLowK FROM gongzi;

所有SQL测试语句：

SELECT * FROM gongzi;

SELECT id, name, gw, substring_index(gw,’-’,1) as low, substring_index(gw,’-’,-1) as high FROM gongzi;

SELECT id, name, gw, Replace(substring_index(gw,’-’,1),‘k’,’’) as low, substring_index(gw,’-’,-1) as high FROM gongzi;

SELECT id, name, gw, substring_index(gw,’-’,1) as low, substring_index(gw,’-’,-1) as high, Replace(substring_index(gw,’-’,1),‘k’,’’) as deleteLowK FROM gongzi;

SELECT id, name, gw, substring_index(gw,’-’,1) as low, substring_index(gw,’-’,-1) as high, Replace(substring_index(gw,’-’,1),‘k’,’’) as deleteLowK FROM gongzi;

最后的说明：

以上三个步骤是连一起操作的，主要目的是演示按特定字符切割，和删除特定字符。