Copy Code code as follows:
Update dede_addonsoft SET dxylink=replace (Dxylink, '. zip ', '. rar ') where aid > 45553;
Copy Code code as follows:
Update ' table_name ' SET field = replace (field, '. rar ', '. 7z ');
TABLE_NAME: The name of the table to query,
Field name in the list,
Replace (field, '. rar ', '. 7z '); : Regular match, replacing. rar in field fields with. 7z
MySQL Regular expression substitution, character substitution method
Two SQL, are character replacement, more useful.
Update comment set url=if (url REGEXP ' test.yahoo.com.cn ', replace (URL, ' www1.sohu.com ', ' www.sina.com '), replace (URL, ' Www2.yahoo.com ', ' www.sina.com ') where 1=1;
Update comment Set Author_url=replace (author_url, ' Sohu ', ' Sina ') where Author_url REGEXP ' www.sohu.com ';
MySQL Replace function replacement string
MySQL Replace function we often use, the following is a detailed description of the use of MySQL replace function, I hope that you learn the MySQL replace function can be enlightened.
Recently in the study of CMS, in the data conversion needs to use MySQL MySQL replace function, here a brief introduction.
For example, you want to replace the ABC of the F1 field in the table TB1 with Def
UPDATE tb1 SET f1=replace (F1, ' abc ', ' Def ');
REPLACE (STR,FROM_STR,TO_STR)
All occurrences of the string from_str in string str are replaced by TO_STR, which then returns the string:
mysql> SELECT REPLACE (' www.mysql.com ', ' w ', ' Ww ');
-> ' WwWwWw.mysql.com '
This function is multibyte-safe.
Example:
UPDATE ' dede_addonarticle ' SET BODY = REPLACE (body,
' </td> ',
'' );
UPDATE ' dede_addonarticle ' SET BODY = REPLACE (body,
' </tr> ',
'' );
UPDATE ' dede_addonarticle ' SET BODY = REPLACE (body,
' <tr> ',
'' );
UPDATE ' dede_archives ' SET title= REPLACE (title,
' Oceanic News-',
'' );
UPDATE ' dede_addonarticle ' SET BODY = REPLACE (body,
'.. /.. /.. /.. /.. /.. /',
' http://special.dayoo.com/meal/');
MySQL Replace
Usage 1.replace intoreplace into table (id,name) VALUES (' 1 ', ' AA '), (' 2 ', ' BB ')
The purpose of this statement is to insert two records into table tables.
2.replace (object, Search,replace)
Replace all occurrences of search in object with Replaceselect replace (' www.163.com ', ' w ', ' Ww ')--->www wWw.163.com
For example: Replace AA in the Name field in table tables with Bbupdate table set Name=replace (name, ' AA ', ' BB ')
