MySQL string function Locate and replace combat

For the locate function in MySQL, you can view the link: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_locate

For the Replace function in MySQL, you can view the link: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

That is the two functions we are going to use today.

Use background:

Condition: The Body_value value of the Field_data_body table has such a regular string "/eapdomain/userfiles/1073/file/", "/eapdomain/userfiles/1073/file/" , where 1073 is the user ID. Don't be surprised why there is such a path!

Objective: To replace all the strings in Body_value with the "/sites/default/files/old/image/" and "/sites/default/files/old/file/".

Difficulty: The amount of data is slightly larger than 4,000 data, it is impossible to manually change, there may be more than one such path or not exist in each piece of data, the user ID is variable and uncertain.

Operate the database directly!

In Navicat, open your database command line tool (not limited to this tool), and enter the following command to replace the job.

Replace the string containing image

UPDATE field_data_body Set body_value = replace (body_value, substring (body_value, locate ('/eapdomain/userfiles/'), body _value), locate ('/image/', Body_value) +7-locate ('/eapdomain/userfiles/', body_value)), '/sites/default/files/old/ image/')

Replace the string containing file

UPDATE field_data_body Set body_value = replace (body_value, substring (body_value, locate ('/eapdomain/userfiles/'), body _value), locate ('/file/', Body_value) +6-locate ('/eapdomain/userfiles/', body_value)), '/sites/default/files/old/ file/')

There are many ways to achieve this, and you are welcome to leave a message below.