[Mysql] REPLACE函数_mysql replace-CSDN博客

本文链接：https://blog.csdn.net/Hudas/article/details/123700010

本文介绍MySQL中REPLACE函数的基本用法及实例演示，通过替换字符串中的特定字符或子串来更新数据。以手机号码替换为例，展示了如何批量更改学生表中的电话号码前缀。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

REPLACE函数可进行字符串替换操作

格式: REPLACE(str,from_str,to_str)

将字符串to_str替代字符串str中的字符串from_str,返回替换后的字符串str

-- 结果是http:www.google.com.cn
SELECT REPLACE('http://www.google.com.cn/','/','');

-- 将字符串abc中的字符a替换为字符x
SELECT REPLACE('abc','a','x') -- xbc

案例:将手机开头号码136替换成158

导入数据

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `s_id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
  `s_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
  `phone` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '电话',
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `student` VALUES (1, 'Tom', '13603735566');
INSERT INTO `student` VALUES (2, 'Lee', '13603735533');
INSERT INTO `student` VALUES (3, 'Harry', '13603735544');
INSERT INTO `student` VALUES (4, 'Odin', '13603735577');
INSERT INTO `student` VALUES (5, 'Jack', '13603735587');

student表

SELECT s_id, s_name, REPLACE(phone,'136','158')
FROM student;

结果展示: