How to Replace Part of String in Update Query in MySQL

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.

Also read:

How to View Live MySQL Queries
How to Generate Random String in MySQL
How to Import Excel File to MySQL
How to View MySQL Log Files
How to Run MySQLdump Without Locking Tables

Sreeram Sreenivasan

Sreeram has more than 10 years of experience in web development, Python, Linux, SQL and database programming.