MySQL replace () bulk substitution of specified character statements

Replace the welcom to Cdb_pms table subject field with Welcome

The code is as follows	Copy Code
UPDATE ' Cdb_pms ' SET ' Subject ' = REPLACE (' Subject ', ' Welcome to ', ' welcome ') WHERE INSTR (' Subject ', ' Welcome to ') > 0

Replace the Message field in the Cdb_posts table and replace "viewthread.php?tid=3989" with "viewthread.php?tid=16546"

The code is as follows	Copy Code
UPDATE ' cdb_posts ' SET ' message ' = REPLACE (' message ', ' viewthread.php?tid=3989 ', ' viewthread.php?tid=16546 ') WHERE INSTR (' message ', ' viewthread.php?tid=3989 ') > 0;

Remove all spaces

The code is as follows	Copy Code
UPDATE ' es_product ' SET ' pro_pub_time ' = TRIM (' Pro_pub_time ')

Delete all the ' [' or '] ' or '. ' The characters

The code is as follows	Copy Code
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

Replace all Chinese '-' for English '-'

The code is as follows	Copy Code
UPDATE ' es_product ' SET ' pro_pub_time ' = REPLACE (' pro_pub_time ', '-', '-') WHERE INSTR (' pro_pub_time ', '-') > 0

Replace all the years with the '-'

The code is as follows	Copy Code
UPDATE ' es_product ' SET ' pro_pub_time ' = REPLACE (' Pro_pub_time ', ' Year ', '-') WHERE INSTR (' Pro_pub_time ', ' year ') > 0 UPDATE ' es_product ' SET ' pro_pub_time ' = REPLACE (' pro_pub_time ', ' month ', '-') WHERE INSTR (' Pro_pub_time ', ' month ') > 0

Replace all ' 2005-04-' of this type into ' 2005-04-01 '

The code is as follows	Copy Code
UPDATE ' es_product ' SET ' pro_pub_time ' = CONCAT (' Pro_pub_time ', ' a ') WHERE substring_index (' pro_pub_time ', '-',-1) = ' and Length (' pro_pub_time ') > 0 and Length (' Pro_pub_time ') > 5

Replace All ' 2005-' This type into ' 2005-01-01 '

The code is as follows	Copy Code
UPDATE ' es_product ' SET ' pro_pub_time ' = CONCAT (' pro_pub_time ', ' 01-01 ') WHERE INSTR (' pro_pub_time ', '-') > 0 and L Ength (' pro_pub_time ') = 5

Add all '-', but a bit less than 8 to append '-01 '

The code is as follows	Copy Code
UPDATE ' es_product ' SET ' pro_pub_time ' = CONCAT (' pro_pub_time ', ' -01 ') WHERE INSTR (' pro_pub_time ', '-') > 0 and LEN GTH (' Pro_pub_time ') < 8

Change All ' 2005 ' to ' 2005-01-01 '

The code is as follows	Copy Code
UPDATE ' es_product ' SET ' pro_pub_time ' = CONCAT (' pro_pub_time ', ' -01-01 ') WHERE INSTR (' pro_pub_time ', '-') = 0 and Lengt H (' pro_pub_time ') = 4

Finally, all the ' 2005-01-01 ' formats into ' January 2005 '

code is as follows	copy code
UPDATE ' Es_ Product '    SET ' pro_pub_time ' = date_format (' pro_pub_time ', '%y year%m month ') WHERE INSTR (' pro_pub_time ', '-') > 0