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 posts:
- Enable Logging of Slow Queries (Slow Query Log) in MySQL Database
- Change and Reset MySQL root Password
- Installing Web Server in FreeBSD 6.0 with Apache 2.2, MySQL 5.0 and PHP 5 – Part 3
- Reset the Root Password of MySQL Server
- Enable MySQL InnoDB Storage Engine Support in XAMPP Installation
73 Comments
Pages: « Previous 1 2
[...] How to Find and Replace Text in MySQL Database using SQL Funkcjonalna wyszukiwarka mp3 Odpowiedz z cytatem « Poprzedni wątek | Następny wątek » Powered by vBulletin® Version 4.1.10 Copyright © 2012 vBulletin Solutions, Inc. All rights reserved. Content Relevant URLs by vBSEO 3.6.0 Wydawca serwisu: Obsługa techniczna: [...]
Thanks for sharing this useful function but I think its not working to remove line break. Like
replace ( Field , ‘\r\n’ ,” );
Its working. Actually we’ve to try with these also. ‘\n’, ‘\r’, ‘\n\r’.
[...] How to Find and Replace Text in MySQL Database using SQL « My …Apr 23, 2007 … MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be … [...]
[...] The guide uses SQL statements based on MySQL replace() function to modify the database. To run SQL queries, login to MySQL database that houses WordPress tables [...]
First of all thank you very much. This script save my lots of hours. Thanks Again….
EXACTLY what I needed, thanks! Saved me a ton of work changing artisan names after making a filing mistake.
Excellent, tahnk you
Any chance of explaining how to replace a single character? I have a column of prices (ie: $100) and want to remove the dollar sign.
UPDATE products set price = replace(price, ‘$’, ”)
isn’t working because it’s looking for values of “$” without the numbers.
hi i used this and it worked fine, but if i have two “Old Company” it changes both. How can I single out one of them by using something like WHERE to determain which one i want to change?
Just what I was looking for… thanks a lot!
How can I use replace in a string with ” caracter?
Great, this is what I needed
Thank you
Thanks for the examples.
Most helpful, thank you.
One question, though. If you want to find a particular string and then remove it entirely, what do you set in the final setting (aka…‘replace found string with this string’). What goes in-between the ' ?
Can you just leave it blank – essentially having a double ' ' ?
Hopefully this question makes sense.
Jim — Yes two single quotes together will serve to remove the string. In example below I removed a non-printing character, hexadecimal 0D, from a column:
update mytable set myfield=replace(myfield, x'0D', '')
Excellent, thank you!
Thanks,
It solved my problem.
Helpful!! Thank you.
[...] you may want to utilize MySQL replace function to replace the links with old permalink structure in posts, pages and comments to new URLs directly [...]
[...] Posted by Filip incearca asta. Da asta e procesul pe care l-ai descris .. o sa incerc acum sa instalez pluginul .. O sa iti [...]
Yeah, but how do you take anything like a typo of a word and replace all instances of it, like aple to apple?
Firstly, thanks for this, most useful.
is there a way to incorporate REGEX into this so that a range of characters can be used in 'wildcard' type format?