MySQL database string Replacement common statement

Station editing sometimes need to update some of the content to modify, if just a few data, we simply need to go directly to the Web site or MySQL data table can be modified, but if you need to modify a large number of the same string, the need for flexible use of MySQL statements to bulk replacement, Common MySQL Database string substitution statements are as follows

Replace replacement string Implementation method:

The Replace function in MySQL directly replaces a specific string in a field in the MySQL database, and it is no longer necessary to write the function to replace it, which is very convenient to use. MySQL replacement function replace ()

The code is as follows	Copy Code
? UPDATE ' table_name ' SET ' field_name ' = replace (' Field_name ', ' from_str ', ' to_str ') WHERE ' field_name ' like '%from_str% '

Description

table_name--the name of the table
field_name--Field Name
from_str--a string to replace
to_str--replaced with string

Replace the XXXXCCC in the web_cn0797 table subject field with Raisheping

The code is as follows	Copy Code
UPDATE ' web_cn0797 ' SET ' Subject ' = REPLACE (' Subject ', ' XXXXCCC ', ' Raisheping ') WHERE INSTR (' Subject ', ' XXXXCCC ') > 0 replaces the web_cn0797 table's Message field, "viewthread.php?tid=3989"

Replace with "viewthread.php?tid=16546"

The code is as follows	Copy Code
UPDATE ' web_cn0797 ' 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 ', ' [') > 0UPDATE ' es_product ' SET ' pro_pub_time ' = REPLACE (' Pro_pub_time ', '] ', ') WHERE INSTR (' pro_pub_time ', '] ') > 0UPDATE ' es_product ' SET ' pro_pub_time ' = REPLACE (' Pro_pub_time ', '. ', '-') WHERE INSTR (' pro_pub_time ', '. ') > 0

Replace all Chinese '-' for English '-'

UPDATE ' es_product ' SET ' pro_pub_time ' = REPLACE (' pro_pub_time ', '-', '-')
WHERE INSTR (' pro_pub_time ', '-') > 0 replaces all years with '-'

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 ') > 0UPDATE ' 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 ', ' 01 ') 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 LENGTH (' 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 LENGTH (' 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 LENGTH (' pro_pub_time ') = 4

Finally, the ' 2005-01-01′ format ' is ' 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