A common question I get asked is how to find and replace text in a MySQL database. There is a simple answer to that question and it’s staring you in the face. REPLACE.  Here is how to use it properly.

The simple string function REPLACE() allows table data with the matching string (from_string) to be replaced by new string (to_string). This is useful if you need to search and replace a text string which affects many records or rows.

The syntax of REPLACE is REPLACE(text_string, from_string, to_string)

Here are the examples:

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.goitexpert.com’, ‘w’, ‘Ww’);

This statement will return ‘WwWwWw.mysql.com’ as a result.

Tags | Databases, General