mysql条件替换语句,大文数码 » mysql替换,sql语句,sql替换语句,要替换的字符串,mysql替换为,mysql替换成...

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

代码如下:

UPDATE ‘cdb_pms’

SET ‘subject’ = REPLACE(‘subject’, ‘Welcome to’, ‘欢迎光临’)

WHERE INSTR(‘subject’,’Welcome to’) > 0

UPDATE 数据表名 SET 字段名 = replace(字段名， ‘要替换的字符串’, ‘替换为’) WHERE 设定条件;

将ak_items 表 title 字段中的 女装 替换成 女士服装配搭

UPDATE ak_items SET title = REPLACE(title,’女装’,’女士服装配搭’) WHERE INSTR(title,’女装’) > 0;

UPDATE ak_texts SET text = REPLACE(text,’jpfz.net’,’www.jpfz.net’) WHERE INSTR(text,’jpfz.net’) > 0;

UPDATE ak_texts SET text = REPLACE(text,’www.guodaqing.com’,’www.nzhe.net’) WHERE INSTR(text,’www.guodaqing.com’) > 0;

UPDATE ak_texts SET text = REPLACE(text,’blog.guodaqing.com’,’www.guodaqing.com’) WHERE INSTR(text,’blog.guodaqing.com’) > 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

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