Mysql replace function usage

Syntax format:

UPDATE table name
SET field name = REPLACE (
The field value before replacement,
'Replace the previous keyword ',
'Keyword after replacement'
)
WHERE
Field name REGEXP "replacing the previous field value ";

Demo:
(1) replace the content field www.111cn. ne with m.111cn.net with the content field whose primary key id is 9 in the B-blog table:

The code is as follows:	Copy code
UPDATE B _blog SET content = REPLACE ( Content, 'Www .111cn.net ', 'M .111cn.net' ) WHERE Id = 9

(2) replace the following content with blank:

[B] [url] [img] www.111cn. ne [/img] [size = 6] [color = Red] Future events ~ Blog about future events ~ , [/Color] [/size] [/url] [/B]
[B] [url] [img] www.111cn.net [/img] [size = 6] [color = Red] Xsimple: committed to providing Internet solutions [/color] [/size] [/url] [/B]
SQL statement:

The code is as follows:	Copy code
UPDATE B _blog SET message = REPLACE ( Message, Substring ( Message, Locate ('[B] [url] [img]', message ), Locate ('[/url] [/B]', message) + 10-locate ('[B] [url] [img]', message) ), '' ) WHERE Id & gt; = 10000

# Additional notes:

① SUBSTRING (expression, pos, length) character truncation function

Parameters

Expression: A String, binary string, text, image, column, or an expression that contains a column. Do not use expressions that contain aggregate functions.
Pos: an integer that refers to the starting position of the stator string.
Length: an integer that refers to the length of the substring (number of characters to return or the number of bytes ).
Select msg = substring ('blog .fity.cn ', 6, 4)...; // output: msg -- fity

② LOCATE (substr, str, pos) string lookup function, returns the subscript of the string. It supports Chinese characters and is case insensitive.
The first syntax returns the position of substr in the first occurrence of the string str. The second syntax returns the position of the substring substr in the string str, the first occurrence from the pos. If substr is not in str, the return value is 0.
Select locate ('fity ', 'blog .fity.cn'); // output 6

③ In the preceding SQL statement, the number 10 is the sum of the numbers of [/url] [/B] Strings