We often use Find and Replace when editing code or other documents. The MySQL replace string function is just what you need when managing database content.
You should remember that in this instance we are talking about a MySQL function (much like DATE() or MONTH() ) rather then the MySQL Replace statement.
The basic syntax of the MySQL replace string function when doing an UPDATE statement is
UPDATE tbl_name
SET
field_name = REPLACE(field_name,
string_to_find,
string_to_replace)
WHERE
conditions;
For example, lets say you updated your site to https:// but have lots of hard coded internal links in your database. To reolve this you would need to edit each one and swap http:// for https:// .
Another example might be that you changed your domain name or folder structure.
Instead of having to find each entry manually we can make use of the Replace string function and do it all in one query.
UPDATE domains SET url = REPLACE(url, 'http://', 'https://mysql');
finally lets say you changed your job status and need to update anywhere its mentioned in your news_table table. On top of that we only want that change to happen in the category with an id of 2. we could do something like this.
Notice in this example how we added a condition to the end of the query. Adding a condition is not required but can be useful when you don’t want to update everything.
UPDATE news_table SET news_body = REPLACE(news_body, 'trainee lion tamer', 'qualified lion tamer') WHERE news_cat_id = 2;
Should you wish to replace text using a Regular Expression (REGEX) you can use the following method.
UPDATE `table` SET column = REGEXP_REPLACE(column, regex, replacement);
For more information on MySQL Replace see the official docs
https://mariadb.com/kb/en/library/replace-function/
https://dev.mysql.com/doc/refman/8.0/en/replace.html