Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 20-05-2009
Member
 
Join Date: May 2009
Posts: 1,948
Need to find and replace text in MySQL database

I need to replace some certain text in MySQL database engine table data because there are some changes in company name, postcode, URL and some spelling mistake. So far so good, except that the MySQL database contains all the post data, which includes hundreds if not thousands of instances of the [font] tag. Since it's been disabled, it no longer gets parsed, and no it's being printed to the screen, making posts that include it almost illegible.So do any of you guys make me know how to find and replace text in MySQL database using SQL ? Please help. Its very urgent.
Reply With Quote
  #2  
Old 20-05-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: Need to find and replace text in MySQL database

The database engine MySQL has a string function called REPLACE that performes similar to the php function str_replace().This is useful if there is need to search and replace a text string which affects many records or rows.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.

heres how to find and replace text in MySQL database, but first backup your database:-

1. From within PHPMyAdmin, open your database and check the names of the table and field holding the information you want to change.

2. Now click on the SQL tab to search and replace text within your wp-posts table and the post-content field and enter the following with the exact item you want to search for in the third line, and what you want to replace it with in the last line.
Quote:
UPDATE wp_posts SET post_content = REPLACE (
post_content,
'Item to replace here',
'Replacement text here');
3. Make sure EVERYTHING is spelled right and that the names of wp-posts and post_content, or whatever table and fields you are using, match the table and field within your database. Make sure that post_content is listed twice and that they match.

4. Now click the GO or APPLY or whatever the DO IT NOW button looks like in your version of PHPMyAdmin.

5. It will then go through your database?s wp_posts table in the post_content field looking for the first line in the search and replace command, and replacing it with the second line. The results will tell you how many records were changed. Let?s hope it?s the number you anticipated. If not, then restore your backup and start over.
Reply With Quote
  #3  
Old 20-05-2009
Dr. V
 
Posts: n/a
Re: Need to find and replace text in MySQL database

Don't do that unless and until you need it .Remember that it can destroy your blog and database if used incorrectly.If the item you wish to search and replace is found in 20 or fewer posts, fix them manually. Much less invasive and destructive.

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.
Reply With Quote
  #4  
Old 20-05-2009
Member
 
Join Date: Feb 2009
Posts: 152
Re: Need to find and replace text in MySQL database

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Need to find and replace text in MySQL database"
Thread Thread Starter Forum Replies Last Post
How to find replace text in Excel Chini mao Windows Software 3 07-01-2012 11:44 AM
How to speed up the MySQL database Kalanidhi Software Development 5 27-02-2010 01:33 AM
Mysql Error : Can't connect to local mysql server through socket ' var lib mysql mysql.sock' 2 roshan45 Software Development 3 07-11-2009 09:36 PM
Restoring MySQL database Sean J Software Development 5 18-02-2009 01:46 PM
How to Connect MySQL database from PHP Booth Software Development 3 21-01-2009 09:12 PM


All times are GMT +5.5. The time now is 11:35 AM.