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 

Tags: , , , , ,

  • Digg
  • Del.icio.us
  • StumbleUpon
  • Reddit
  • Twitter
  • RSS

Leave a Reply

You must be logged in to post a comment.

文字链