Monday, September 10, 2012

How to Find and Replace Text in MySQL Database using SQL



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.

No comments:

Post a Comment

Labels

AJAX (1) Answers (1) Apache (1) Array (16) bug (1) C (1) C++ (1) Calendar (1) Class (1) Commands (1) Cookies (2) Database (2) Date (7) Days (1) explode (1) File Upload (1) FILES (1) firewall (1) fix (1) Functions (26) GET (1) GMT (1) JavaScript (2) localhost (1) Mail (1) Menu (1) MYSQL (13) PERL (1) PHP (36) php.ini (1) POST (1) preg_match (1) Questions (1) Script (1) SMS (2) Solution (1) String (1) Time (5) Time Zone (1) Vista (1) Wamp Server (1) windows 7 (2) XML (1)

Popular Posts

Popular Posts