How to Replace a String Portion within a MySQL Column's Values?

Replacement of string part in MySQL database column value

In a MySQL database, you may need to update multiple rows in a column named "url" to change the domain name in the URL value. The goal is to replace "domain1.example/images/" with "domain2.example/otherfolder/" while preserving the filename. Here's how to achieve this:

You can execute the following query to update the "url" column:

<code class="language-sql">UPDATE urls
SET url = REPLACE(url, 'domain1.example/images/', 'domain2.example/otherfolder/')
WHERE url LIKE 'domain1.example/images/%';</code>

This query uses the REPLACE() function, which replaces a specified substring in a string value. In this example, the substring "domain1.example/images/" in the "url" column will be replaced by "domain2.example/otherfolder/". To avoid unnecessary updates, a WHERE clause is added to update only URLs containing 'domain1.example/images/'.

REPLACE() The function accepts three parameters:

1. The string to update ("url" in this case)
2. The substring to replace ("domain1.example/images/")
3. New substring to replace ("domain2.example/otherfolder/")

With this modified query, it is ensured that only the target URL is updated, improving the accuracy and security of data updates.

The above is the detailed content of How to Replace a String Portion within a MySQL Column's Values?. For more information, please follow other related articles on the PHP Chinese website!