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
UPDATE table_name SET column_name = REPLACE(column_name, 'old_string', 'new_string') WHERE column_name LIKE ('%old_string%');
column_nameis the column in which you would like to replace the string
old_stringis the string you would like to replace
new_stringis the string that will replace with the
- 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.
Let us replace the string ‘9876543210’ with ‘0123456789’ in column
Run the query to replace the old string with new string in
UPDATE sampletable SET sometext = REPLACE(sometext, '9876543210', '0123456789')
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.
UPDATE sampletable SET sometext = REPLACE(sometext, '9876543210', '0123456789') WHERE sometext LIKE ('%9876543210%');
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).
In this MySQL Tutorial, we have learnt to replace string in the cell values of a column.