How Can I Update Parts of Strings in MySQL with a Single Query?

MySQL: Update a Portion of a String via a Single Query

Introduction:
Many applications require the ability to modify specific portions of a string within a database. This task can become cumbersome if multiple rows need to be updated individually. MySQL provides a convenient solution to this challenge.

Solution:
To update a portion of a string for multiple rows in a single query, MySQL offers the REPLACE() function. This function replaces all occurrences of a specified substring with a new value.

Syntax:

UPDATE table_name
SET column_name = REPLACE(column_name, 'substring_to_replace', 'new_substring')
WHERE condition;

Example:
Consider the following table with a field named "description":

To replace all occurrences of "string" with "anothervalue" in all rows, use the following query:

UPDATE table_name
SET description = REPLACE(description, 'string', 'anothervalue');

Result:

Note: If you wish to only update rows that contain the specified substring, include a WHERE clause in your query. For example, to update only rows where "string" appears in the "description" field:

UPDATE table_name
SET description = REPLACE(description, 'string', 'anothervalue')
WHERE description LIKE '%string%';

The above is the detailed content of How Can I Update Parts of Strings in MySQL with a Single Query?. For more information, please follow other related articles on the PHP Chinese website!