Quickly update all content in mySQL database replacing a string

: Joel Lipman; MySQL; 03 February 2013; Hits: 5191

What?
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.

Why?
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.

How?
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>';

 SELECT   
      `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>';

UPDATE
	`MYTABLE_content`
SET
	`introtext` = REPLACE(`introtext`, @stringtoFind, @stringtoReplaceWith)
WHERE  
	`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>';

UPDATE
	`MYTABLE_content`
SET
	`introtext` = REPLACE(`introtext`, @stringtoFind, @stringtoReplaceWith),
	`fulltext` = REPLACE(`fulltext`, @stringtoFind, @stringtoReplaceWith)
WHERE  
	`introtext` LIKE CONCAT('%',@stringtoFind,'%')
OR
	`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