MySQL Replace and replace into usage

MySQL Replace instance description:

UPDATE tb1 SET f1=replace (F1, ' abc ', ' Def ');
REPLACE (STR,FROM_STR,TO_STR)
All occurrences of the string from_str in the string str are replaced by TO_STR, and then the string is returned

This function is useful for bulk substitution of illegal keywords in data! Here's an example:

Example 1:update bbstopic SET tcontents = replace (replace (tcontents, ' handsome ', '), ' Find dead ', ') where tcontents like '% handsome ' or tcontents l Ike '% find dead% '
Example 2:update typetable SET type_description=replace (type_description, ' a ', ' http://www.jb51.net ');

MySQL Replace usage

1.replace into
Replace into table (Id,name) VALUES (' 1 ', ' AA '), (' 2 ', ' BB ')
The purpose of this statement is to insert two records into table tables. If the primary key ID is 1 or 2 does not exist
is equivalent to
Insert into table (Id,name) VALUES (' 1 ', ' AA '), (' 2 ', ' BB ')
Data is not inserted if the same value exists

2.replace (Object,search,replace)

Replace all occurrences of search in object with replace

Select replace (' Www.jb51.net ', ' w ', ' Ww ')--->wwwwww.jb51.net

Example: Replacing AA in the Name field in table tables with BB

Update table Set Name=replace (name, ' AA ', ' BB ')

MySQL Replace function we often use, the following gives you a detailed description of the use of MySQL replace function, I hope you learn MySQL Replace function can be enlightened.

Recently in the study of CMS, in the data conversion need to use the MySQL MySQL replace function, here is a brief introduction.

For example, you want to replace the ABC of the F1 field in the table tb1 with the Def

UPDATE tb1 SET f1=replace (F1, ' abc ', ' Def ');
REPLACE (STR,FROM_STR,TO_STR)
All occurrences of the string from_str in the string str are replaced by TO_STR, and then the string is returned:
mysql> SELECT REPLACE (' www.mysql.com ', ' w ', ' Ww ');
' WwWwWw.mysql.com '
This function is multi-byte 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,
' Ocean 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.jb51.net ', ' w ', ' Ww ')--->www wWw.jb51.net

Example: Replacing AA in the Name field in table tables with Bbupdate table set Name=replace (name, ' AA ', ' BB ')

MySQL Replace and replace into usage