Need to find and replace text in MySQL database

[Adamaris]

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.

[kelfro]

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.

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.

[Dr. V]

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.

[aalbaiz]

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