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
- Remove or Trim First or Last Few Characters in MySQL Database with SQL
- Check and Optimize MySQL Database Automatically with Crontab/Cron
- MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length
- MySQL Database Performance Tuning Best Practices Video Tutorial
- Enable Logging of Slow Queries (Slow Query Log) in MySQL Database
- Change and Reset MySQL root Password
- Replace Notepad with Another Text Editor (eg. Notepad2 and Notepad++) in Vista
- Download File Content Replacer to Search and Replace Text in Multiple Files
- Using PHP-MySQL Persistent Connections to Run WordPress Blog
- How to Backup and Restore (Export and Import) MySQL Databases Tutorial
Entries (RSS)
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