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;
最后的说明:
以上三个步骤是连一起操作的,主要目的是演示按特定字符切割,和删除特定字符。