Example of replacing SQL statements in mysql

If you need to partially replace the content in mysql, you can refer to the following article.

Replace Welcom to in the subject field of the cdb_pms table with welcome

UPDATE 'cdb _ pms 'set' subobject' = REPLACE ('subobject', 'Welcome to ', 'Welcome to') where instr ('subobject', 'Welcome to ')> 0

Replace the message field in the cdb_posts table and set "viewthread. php? Tid = 3989 "with" 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;

Delete all spaces

UPDATE 'es _ product' SET 'Pro _ pub_time '= TRIM ('Pro _ pub_time ')

Delete all characters that contain '[' or ']' or '.'

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 characters '-' with English '-'

UPDATE 'es _ product' SET 'Pro _ pub_time '= REPLACE ('Pro _ pub_time', '-', '-') where instr ('Pro _ pub_time ', '-')> 0

Replace all the months '-'

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 '2017-04-'with '2017-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

Replace all '1970-'with '1970-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

Change all values that are full of '-' but whose digits are smaller than 8 to append '-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

Change all '123' to '1970-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

Finally, format all '2017-01-01 'to '2017'

UPDATE 'es _ product' SET 'Pro _ pub_time '= DATE_FORMAT ('Pro _ pub_time', '% Y % mmonth') where instr ('Pro _ pub_time ', '-')> 0