Replace the welcom to Cdb_pms table subject field with Welcome
Copy CodeThe code is as follows:
UPDATE ' Cdb_pms '
SET ' Subject ' = REPLACE (' Subject ', ' Welcome to ', ' welcome ')
WHERE INSTR (' Subject ', ' Welcome to ') > 0
Replace the message field of the Cdb_posts table with "viewthread.php?tid=3989" replaced by "viewthread.php?tid=16546"
Copy CodeThe code is as follows:
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 the spaces
Copy CodeThe code is as follows:
UPDATE ' es_product ' SET ' pro_pub_time ' = TRIM (' Pro_pub_time ')
Remove all ' [' or '] ' or '. ' The character
Copy CodeThe code is as follows:
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 English '-' with Chinese '-'
Copy CodeThe code is as follows:
UPDATE ' es_product ' SET ' pro_pub_time ' = REPLACE (' pro_pub_time ', '-', '-') WHERE INSTR (' pro_pub_time ', '-') > 0
Replace all the years with '-'
Copy CodeThe code is as follows:
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 with ' 2005-04-01 '
Copy CodeThe code is as follows:
UPDATE ' es_product ' SET ' pro_pub_time ' = CONCAT (' pro_pub_time ', ' ") WHERE substring_index (' pro_pub_time ', '-',-1) = ' ' and length ' (' pro_pub_time ') > 0 and Length (' Pro_pub_time ') > 5
Replace All ' 2005-' This type with ' 2005-01-01 '
Copy CodeThe code is as follows:
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 ') = 5
Add all '-', but the number of bits less than 8 to ' 01 '
Copy CodeThe code is as follows:
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 ' 2005 ' to ' 2005-01-01 '
Copy CodeThe code is as follows:
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, all ' 2005-01-01 ' is formatted as ' January 2005 '
Copy CodeThe code is as follows:
UPDATE ' es_product ' SET ' pro_pub_time ' = date_format (' pro_pub_time ', '%y year%m month ') WHERE INSTR (' pro_pub_time ', '-') > 0
MySQL partial replacement SQL statement sharing
