How to find and replace text in the MySQL database using SQL
December 31st, 2008 | by whazup |
MySQL database is a simple and useful string function REPLACE () which allows data to the table with the strings (from_string) to be replaced by new string (to_string). This is useful if you need to find and replace a text string that affects many rows or records, such as change of name, zip code, URL, or spelling.
The syntax of REPLACE is REPLACE(text_string, from_string, to_string)
Reference described in REPLACE function that returns the string text_string with all occurrences of the string from_string replaced by the chain to_string, where correspondence is sensitive when looking for from_string. text_string can be obtained from the field in the database table as well. Most SQL queries can be REPLACE() function, and in particular the handling SELECT and UPDATE.
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’, ‘x’);
Above statement will return ‘xxx.mysql.com’ as result.
Related Posts:
Tags: database, Find, How, mySQL, replace, SQL, text
