How to replace a string with MySQL Query

Maybe you think this is a stupid article . but the reason for this article is to stop forgetting the syntax and to Google to find the exact location of the parameters in the MySQL REPLACE command. Have you ever used Google but still end the document page with MySQL's REPLACE command . used to update the row in the table, with the same syntax as the INSERT command? Yes, thanks for making that mistake, MySQL!

1. 13 important SQL statements Programmer needs to know

Try to imagine this situation, you must try to find and replace on your WordPress database with a simple query to transfer the website (such as TipsMake.com) to HTTPS, you have the goods. tons of links and images pointing to HTTP, so all links must be updated to with nearly 20,000 records in the database, and may need to hire up to 100 trainees to do that . Or just use an SQL statement.

So now, this article will repeat the exact syntax to replace a string using the MySQL query, so you don't have to hire 100 more trainees:

UPDATE table_name set name_type = REPLACE (name_type, 'string_cần_Search', '' string_that_the ');

Going back to the above example, we need to update all links and images to HTTPS in WordPress:

UPDATE wp_posts set post_content = REPLACE (post_content, 'http:///www.quantrimang.com', 'https: //wwwquantrimang.com');

If you're a new MySQL user, you don't know how to open the MySQL command prompt, oh, you probably shouldn't read this article. Either open the terminal, use the values ​​in wp-config.php (assuming you're using WordPress) to connect to the database and then paste the SQL statement into it.

mysql -uUser -pPassword -hHost databasename

Remember to back up the data before running the command, if possible, test it in the previous test environment, as you are about to change a large part of that database. Don't let me turn into an idiot when the command ends!