Replace String in MySQL

You can replace a string for matches only in specific column of MySQL table.

In this tutorial, we shall learn to replace a string in a column with the SQL query and examples.

Syntax – Replace String

The syntax to replace a string in a column in SQL is

</>
Copy
UPDATE table_name
SET column_name = REPLACE(column_name, 'old_string', 'new_string')
WHERE column_name LIKE ('%old_string%');

where

  • column_name is the column in which you would like to replace the string
  • old_string is the string you would like to replace
  • new_string is the string that will replace with the old_string
  • WHERE clause is optional, but you can use if for performance. Also you can extra filter the rows where you have to replace the string.

Example 1 – Replace String in Column

Consider the following sampletable MySQL Table.

mysql replace string in column example

Let us replace the string ‘9876543210’ with ‘0123456789’ in column sometext.

Run the query to replace the old string with new string in sometext column.

</>
Copy
UPDATE sampletable
SET sometext = REPLACE(sometext, '9876543210', '0123456789')
mysql replace old string with new string in column

You see that 3 rows got affected. But number of Rows matched is 4. This is where the WHERE clause boosts the performance.

We shall try the same REPLACE function with WHERE clause. This is for performance improvement.

</>
Copy
UPDATE sampletable
SET sometext = REPLACE(sometext, '9876543210', '0123456789')
WHERE sometext LIKE ('%9876543210%');
mysql replace string in column with where clause for performance

The number of rows matched is 3, and also the time taken for REPLACE function with WHERE clause is 0.03 seconds(which is way less than 0.12 seconds in the previous case).

Conclusion

In this MySQL Tutorial, we have learnt to replace string in the cell values of a column.