Quickly update all content in mySQL database replacing a string

This is an article on how to replace a string with another in all your joomla articles without modifying other data such as modified date and author.

I used to have a third-party plugin that would replace my HTML tags <PRE> with <PRE CLASS="brush:php">. Not I want to switch these back.

For demo purposes, I'm searching a column called introtext in a database table called MYTABLE_content. First let me find all the articles that need this change:
SET @stringtoFind='<pre class="brush:php">';
SET @stringtoReplaceWith='<pre>';

      `id` , 
      `title` , 
      IF(LOCATE( @stringtoFind, REPLACE(`content_column`, @stringtoFind, @stringtoReplaceWith) )>0,'yes','no') as OldFoundInNew, 
      IF(LOCATE( @stringtoReplaceWith, `content_column`)>0,'yes','no') as NewFoundInOld, 
      IF(LOCATE( @stringtoReplaceWith, REPLACE(`content_column`, @stringtoFind, @stringtoReplaceWith) )>0,'yes','no') as OldFoundInNew, 

      SUBSTRING(`content_column`, LOCATE( @stringtoFind, `content_column` )) as OldFind, 

      REPLACE(`content_column`, @stringtoFind, @stringtoReplaceWith) AS newText, 
      `content_column` as oldText 
 FROM  `MYTABLE_content`  
 WHERE  `content_column` LIKE CONCAT('%',@stringtoFind,'%')

-- yields
id	title		OldFoundInOld	NewFoundInOld	OldFoundInNew	newText							oldText
1	Article 1	yes		no		no		<pre class="brush:php">Hello World</pre>	<pre>Hello World</pre>
2	Article 2	yes		no		no		<pre class="brush:php">Hello World</pre>	<pre>Hello World</pre>
3	Article 3	yes		no		no		<pre class="brush:php">Hello World</pre>	<pre>Hello World</pre>

Now update them
SET @stringtoFind='<pre class="brush:php">';
SET @stringtoReplaceWith='<pre>';

	`introtext` = REPLACE(`introtext`, @stringtoFind, @stringtoReplaceWith)
	`introtext` LIKE CONCAT('%',@stringtoFind,'%')

Run the first query again and no rows should be returned.

Quick Joomla One
I use this reluctantly but often necessarily.
SET @stringtoFind='<pre class="brush:php">';
SET @stringtoReplaceWith='<pre>';

	`introtext` = REPLACE(`introtext`, @stringtoFind, @stringtoReplaceWith),
	`fulltext` = REPLACE(`fulltext`, @stringtoFind, @stringtoReplaceWith)
	`introtext` LIKE CONCAT('%',@stringtoFind,'%')
	`fulltext` LIKE CONCAT('%',@stringtoFind,'%')

You might get some errors talking about some empty rows but don't be deceived as the update will have happened anyway.

Other Searches
  1. Search and Replace a string in a mySQL database.

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman

Related Articles

Joes Revolver Map


Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Donate to Joel Lipman via PayPal

Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.