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

This entry was posted on Monday, April 23rd, 2007 at 4:02 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.