MySQL allows you to easily update your database columns using UPDATE statement. It also allows you to replace strings using REPLACE function. But can we replace part of string in update query in MySQL? In this article, we will learn how to do this. Sometimes, database developers need to replace part of a column value or a literal string. In such cases, you can use the steps mentioned below.
How to Replace Part of String in Update Query in MySQL
Let us say you have a database table named visitors(id, location, landing_url). Let us say the values in landing_url begin with https://example.com (e.g. http://example.com/product/1, http://example.com/about-us, etc.) and you want to replace this part with http://www.mysite.com. You can easily do this using a combination of UPDATE statement and REPLACE function as shown below.
UPDATE visitors SET url = REPLACE(url, 'example.com', 'mysite.com');
Now all URLs that begin with https://example.com (e.g. https://example.com/product/1, https://example.com/about-us, etc.) will be begin with https://mysite.com (e.g. http://mysite.com/product/1, http://mysite.com/about-us, etc.)
You can also use WHERE condition in your UPDATE statement, if you only want to replace some but not all column values in your table.
UPDATE visitors SET url = REPLACE(url, 'example.com', 'mysite.com') WHERE id in (1,4,10);
You can also use REPLACE() function on a literal string as shown below.
SELECT REPLACE('www.example.com', 'example.com', 'mysite.com');
In this article, we have learnt a simple way to replace part of string in UPDATE query in MySQL.