How to Find and Replace Text in MySQL Database using SQL
MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be replaced by new string (to_string). This is useful if there is need to search and replace a text string which affects many records or rows, such as change of company name, postcode, URL or spelling mistake.
The syntax of REPLACE is REPLACE(text_string, from_string, to_string)
MySQL reference describes REPLACE as function that returns the string text_string with all occurrences of the string from_string replaced by the string to_string, where matching is case-sensitive when searching for from_string. text_string can be retrieved from the a field in the database table too. Most SQL command can be REPLACE() function, especially SELECT and UPDATE manipulation statement.
For example:
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);
update client_table set company_name = replace(company_name, ‘Old Company’, ‘New Company’)
The above statement will replace all instances of ‘Old Company’ to ‘New Company’ in the field of company_name of client_table table.
Another example:
SELECT REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’);
Above statement will return ‘WwWwWw.mysql.com’ as result.
Related Articles
- Change and Reset MySQL root Password
- How to Move WordPress Blog to New Domain or Location
- Install Web Server in Windows XP with Apache2, PHP5 and MySQL4 – Part 4
- MySQL Database Performance Tuning Best Practices Video Tutorial
- Check and Optimize MySQL Database Automatically with Crontab/Cron
- How to Backup and Restore (Export and Import) MySQL Databases Tutorial
- Change or Set MySQL Long Query Time Value for log-slow-queries
- Installing Web Server in FreeBSD 6.0 with Apache 2.2, MySQL 5.0 and PHP 5 – Part 3
- MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length
- Remove or Trim First or Last Few Characters in MySQL Database with SQL










































February 8th, 2010 21:16
Great! Problem solved.
January 15th, 2010 06:37
[...] If there are matches found then you need to click the SQL tab at the top (preferably in a new tab) and paste the following (making sure to edit it to match the table and fields you need) and run it to find and replace that changed part of the path. update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’); For more information on SQL find and replace, either search google or read this. [...]
January 1st, 2010 23:39
Thanks! Just what I was looking for.
December 30th, 2009 05:12
this helped me a lot, thanks! :]
December 23rd, 2009 23:22
I need to replace some old links that are in the database records that used some javascript and contain % characters as part of the string. I can’t seem to get replace to recognize these and I am assuming it treats them like a wildcard char in mysql. I’ve tried escaping \% etc but not getting results.
any suggestions
example (these are not intended to be wildcard characters):
update tablename set content = replace(content,’%200,%210,%200′,”);
December 22nd, 2009 13:09
Remember that when using replace() to update a field (like the old company / new company example), the length of the field does not necessarily expand to fit the new content.
December 20th, 2009 02:57
This worked great for removing all the  characters that showed up on my wordpress blog after migrating my database into a mysql 5.0 db. Thank you!
December 18th, 2009 02:52
Hi Thanks, just the ticket
November 27th, 2009 21:49
This was pretty useful when I migrated standalone blogs to WPMU, and I needed to replace bunch of media file paths.
Thanks.
November 24th, 2009 00:00
@Richard:
Just a guess: are you using regular single quotes (i.e. apostrophe) as you should, or maybe typographic ones, which you shouldn’t?
November 20th, 2009 14:16
I’m moving my blog to a new hosting company. Since I’m moving the blog to the root from a subfolder /blog/, I need to change the wp_posts guid field on all the posts.
Example: http://mymarketingvp.net/blog/18-revision-5/ [This is from the current database.]
Following the steps above, I entered this SQL statement:
update wp_posts set guid = replace(guid, ‘net/blog/’, ‘net/’)
["net" is the end of the domain name *.net]
I got the following error message:
Error
SQL query:
UPDATE wp_posts SET guid = replace( guid, ‘net / blog / ’, ‘net / ’ )
MySQL said:
#1054 – Unknown column ‘‘net’ in ‘field list’
Notice how it added some spaces in the command. Same error when I remove the spaces.
Any suggestions??? Thanks!
October 28th, 2009 09:00
I am looking for a way to do this globally accross a whole database.
October 20th, 2009 20:28
Doesn’t work at all for replacing web addresses within a database.
September 30th, 2009 15:26
Wanted to use REPLACE command to use for a column of a table…and this helped me…thanks