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:

Syntax:

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

Joel G Mathew, known in tech circles by the pseudonym Droidzone, is an opensource and programming enthusiast.

He is a full stack developer, whose favorite languages are currently Python and Vue.js. He is also fluent in Javascript, Flutter/Dart, Perl, PHP, SQL, C and bash shell scripting. He loves Linux, and can often be found tinkering with linux kernel code, and source code for GNU applications. He used to be an active developer on XDA forums, and his tinkered ROMS used to be very popular in the early 2000s.

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.