【分享】mysql的部分替换sql语句

有时候需要对mysql中的内容进行部分替换，那么可以参考下面的文章。

 

将cdb_pms表subject字段中的Welcom to替换成 欢迎光临

UPDATE cdb_pms
SET subject = REPLACE(subject, 'Welcome to', '欢迎光临')
WHERE INSTR(subject,'Welcome to') > 0

 

替换cdb_posts表的message字段，将“viewthread.php?tid=3989”替换成“viewthread.php?tid=16546”

UPDATE cdb_posts
SET message= REPLACE(message, 'viewthread.php?tid=3989', 'viewthread.php?tid=16546')
WHERE INSTR(message,'viewthread.php?tid=3989') > 0 ;

 

删除所有的空格

UPDATE es_product SET pro_pub_time = TRIM(pro_pub_time)

 

删除所有饱含'[‘或者’]’或者’.’的字符

UPDATE es_product SET pro_pub_time = REPLACE(pro_pub_time, '[','') WHERE INSTR(pro_pub_time,'[') > 0 
UPDATE es_product SET pro_pub_time = REPLACE(pro_pub_time, ']','') WHERE INSTR(pro_pub_time,']') > 0
UPDATE es_product SET pro_pub_time = REPLACE(pro_pub_time, '.','-') WHERE INSTR(pro_pub_time,'.') > 0

 

将所有的年月都替换成’-‘

UPDATE es_product SET pro_pub_time = REPLACE(pro_pub_time, '年','-') WHERE INSTR(pro_pub_time,'年') > 0
UPDATE es_product SET pro_pub_time = REPLACE(pro_pub_time, '月','-') WHERE INSTR(pro_pub_time,'月') > 0

 

将所有’2005-04-‘这种类型的替换成’2005-04-01’

UPDATE es_product SET pro_pub_time = CONCAT( pro_pub_time, '01') WHERE SUBSTRING_INDEX( pro_pub_time, '-', -1) = '' AND LENGTH(pro_pub_time) > 0 AND LENGTH(pro_pub_time) > 5

 

将所有’2005-‘这种类型替换成’2005-01-01’

UPDATE es_product SET pro_pub_time = CONCAT( pro_pub_time, '01-01') WHERE INSTR(pro_pub_time,'-') > 0 AND LENGTH(pro_pub_time) = 5

 

将所有 饱含’-‘,但是位数小于8的改成追加’-01′

UPDATE es_product SET pro_pub_time = CONCAT( pro_pub_time, '-01') WHERE INSTR(pro_pub_time,'-') > 0 AND LENGTH(pro_pub_time) < 8

 

将所有'2005'这样的改成'2005-01-01'

UPDATE es_product SET pro_pub_time = CONCAT(pro_pub_time,'-01-01') WHERE INSTR(pro_pub_time,'-') = 0 AND LENGTH(pro_pub_time) = 4

 

最后将所有'2005-01-01'格式化成'2005年01月'

UPDATE es_product SET pro_pub_time = DATE_FORMAT(pro_pub_time,'%Y年%m月') WHERE INSTR(pro_pub_time,'-') > 0