MySQL replacement string bulk Replace SQL statement

Replace replacement

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 ()

Replace

Replace (str1, str2, STR3): In the string str1,? STR2 out?? R,?⑵ Huang Str3 substitution.

Example

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

For example:

MySQL replaces the table with the contents of the field, such as examples:

The code is as follows	Copy Code
Mysql> Select Id,type from items limit 10; +--------+--------+ | ID | Type | +--------+--------+ | 0001 | 780000 | | 0002 | 780000 | | 0003 | 780000 | | 0004 | 780000 | | 0005 | 780000 | | 150419 | 780000 | | 150420 | 780000 | | 150421 | 780000 | | 150422 | 780000 | | 150423 | 780000 | +--------+--------+

Change the "78" in the Type field to "79" with the Replace function

SQL is as follows:

The code is as follows	Copy Code
mysql> Update Items Set Type=replace (type, ' 79 ', ' 78 '); Query OK, 17536 rows affected (0.72 sec) Rows matched:17536 changed:17536 warnings:0

Query again:

The code is as follows	Copy Code
Mysql> Select Id,type from items limit 10; +--------+--------+ | ID | Type | Www.111cn.net +--------+--------+ | 0001 | 790000 | | 0002 | 790000 | | 0003 | 790000 | | 0004 | 790000 | | 0005 | 790000 | | 150419 | 790000 | | 150420 | 790000 | | 150421 | 790000 | | 150422 | 790000 | | 150423 | 790000 | +--------+--------+ Rows in Set (0.00 sec)

From query results to, data has been updated successfully

Regular replacement

Locate

LOCATE (SUBSTR,STR)
POSITION (substr in str)
Returns the position of the substring substr the first occurrence in string str. If substring substr does not exist in STR, the return value is 0:

Substring

SUBSTR (Str,pos,len): Starting from <pos> position in <str>, the next <len> characters are selected.

First of all, describe the problems I encountered:

The following is a table in the database mt2:
+----+------------------------------------------+
| id | name                                       |
+----+------------------------------------------+
|  1 | sdfsf<contact>beijing</contact> sldjfsld  |
|  2 | sdfsf<contact>shanghai</contact>sldjfsld |
|  3 | sdfsf<contact>jn</contact>sldjfsld       |
|  4 | sdfsf<contact>qd</contact>sldjfsld       | The requirement that
+----+------------------------------------------+
is encountered is to delete the contents of <contact> to </contact> in the table.
As we all know, the Replace function does not support regular expressions, so it can only be handled in other ways.
So, I used the following SQL statement:
SQL code  

The code is as follows	Copy Code
Update MT2 Set name = replace (name, substring (name, locate (' <contact> ', name), locate (' </contact> ', name)- Locate (' <contact> ' +10, name), ");

The problem has been solved.
Results:
+----+-------------------+
| ID | name |
+----+-------------------+
| 1 | SDFSFACTSLDJFSLD |
| 2 | SDFSFACTSLDJFSLD |
| 3 | SDFSFACTSLDJFSLD |
| 4 | SDFSFACTSLDJFSLD |
+----+-------------------+