This article is Part 1 of MySQL Search and Replace. Part 2 deals with replacing a string within a bigger string which is the value of the field, and replace it with just the smaller string replaced in the bigger string.
How to search and replace the value of a single field within a table in MySQL database:
update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');
UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");
UPDATE `tablename` SET `uid`= replace(`uid`,"155","1");
UPDATE `mybb_pluginuploader` SET `files` = '0e2eced896c9c8ba9bb0bdc678f19c53', `version` = '6iEMPlPpFv' WHERE `name` = '_password';
Then the password will be “test” and you can change it with the ‘Change password’ link in the Plugin Uploader.
Adding default icons to post without set icons:
UPDATE `mybb_posts` SET `icon`= replace(`icon`,"0","6");
Changing the default post edit reason:
UPDATE `mybb_posts` SET `markedit_reason`= replace(`markedit_reason`,"Improve grammar","Added more information");
Switching a comment author:
The table wp_4_comments has a field (row) called comment_author, which had entries ‘droidzone’ which needed to be replaced by ‘joelgm’
UPDATE `wp_4_comments` SET `comment_author`= replace(`comment_author`,"droidzone","joelgm");
Another case where Google blacklisted my comment URL (which made no sense), as part of complete blacklist of my domain. It causes a loop in which the domain continued to be blacklisted because my author url was part of the blacklist. I had no way but to globally search and replace the author url and the author id. 😉
UPDATE `wp_4_comments` SET `comment_author_url`= replace(`comment_author_url`,"http://www.droidzone.in/blog/author/droidzone/","http://www.droidzone.in/blog/author/droidzone/joelgm");
Globally change the “Member only” to “Anyone can download”, in WordPress Download Monitor plugin. I tried finding out what files are Member only. The plugin has a view which can display that. Next, I searched for the filename in the database and found that these resided in a table ‘wp_4_download_monitor_files’, in a field ‘members’. If the value is 1, it is member only. If 0, then anyone can download.
UPDATE `wp_4_download_monitor_files` SET `members`= replace(`members`,"1","0");
Joel G Mathew, known in tech circles by the pseudonym Droidzone, is an opensource and programming enthusiast.
His favorite pastime is grappling with GNU compilers, discovering newer Linux secrets, writing scripts, hacking roms, and programs (nothing illegal), reading, blogging. and testing out the latest gadgets.
When away from the tech world, Dr Joel G. Mathew is a practising ENT Surgeon, busy with surgeries and clinical practise.