Friday, October 31, 2014
   
Text Size
Login

Quickly update all content in mySQL database replacing a string

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:

  1.  SET @stringtoFind='<pre class="brush:php">'
  2.  SET @stringtoReplaceWith='<pre>'
  3.   
  4.   SELECT    
  5.        `id` ,  
  6.        `title` ,  
  7.     
  8.        IF(LOCATE( @stringtoFindREPLACE(`content_column`@stringtoFind@stringtoReplaceWith) )>0,'yes','no') as OldFoundInNew,  
  9.        IF(LOCATE( @stringtoReplaceWith`content_column`)>0,'yes','no') as NewFoundInOld,  
  10.        IF(LOCATE( @stringtoReplaceWithREPLACE(`content_column`@stringtoFind@stringtoReplaceWith) )>0,'yes','no') as OldFoundInNew,  
  11.   
  12.        SUBSTRING(`content_column`LOCATE( @stringtoFind`content_column` )) as OldFind,  
  13.   
  14.        REPLACE(`content_column`@stringtoFind@stringtoReplaceWithAS newText,  
  15.        `content_column` as oldText  
  16.     
  17.   FROM  `MYTABLE_content`   
  18.     
  19.   WHERE  `content_column` LIKE CONCAT('%',@stringtoFind,'%'
  20.   
  21.   
  22.  -- yields 
  23.  id     title          OldFoundInOld     NewFoundInOld     OldFoundInNew     newText                                   oldText 
  24.  1     Article 1     yes          no          no          <pre class="brush:php">Hello World</pre>     <pre>Hello World</pre> 
  25.  2     Article 2     yes          no          no          <pre class="brush:php">Hello World</pre>     <pre>Hello World</pre> 
  26.  3     Article 3     yes          no          no          <pre class="brush:php">Hello World</pre>     <pre>Hello World</pre> 

Now update them

  1.  SET @stringtoFind='<pre class="brush:php">'
  2.  SET @stringtoReplaceWith='<pre>'
  3.   
  4.  UPDATE 
  5.       `MYTABLE_content` 
  6.  SET 
  7.       `introtext` = REPLACE(`introtext`@stringtoFind@stringtoReplaceWith
  8.  WHERE   
  9.       `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.

  1.  SET @stringtoFind='<pre class="brush:php">'
  2.  SET @stringtoReplaceWith='<pre>'
  3.   
  4.  UPDATE 
  5.       `MYTABLE_content` 
  6.  SET 
  7.       `introtext` = REPLACE(`introtext`@stringtoFind@stringtoReplaceWith), 
  8.       `fulltext` = REPLACE(`fulltext`@stringtoFind@stringtoReplaceWith
  9.  WHERE   
  10.       `introtext` LIKE CONCAT('%',@stringtoFind,'%'
  11.  OR 
  12.       `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.
Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • Joes Quicklist Weblinks (JQW)

    • Mon 27-Oct-14
      Hi. I was able to download today with my laptop in a different location. Both computers using Ubuntu ...
      Webcrawler  
    • Sun 26-Oct-14
      Hmm... thanks Webcrawler! I can't replicate the error as all the links work for both my superuser ...
      Webmaster  
    • Sun 26-Oct-14
      Can't download new version. I get the same error message as Kat a few post's up.... CTRL+F5 does not ...
      Webcrawler  
    • Sat 25-Oct-14
      Hi Traveller, Apologies for the incredible delay as I have been focusing on another app in development.
      Webmaster
  • SSIS Script: convert UPPERCASE to Mixed-Case using TitleCase

    • Tue 28-Oct-14
      This post was immensely helpful, thank you. I used the code for the script component and made some ...
      Dave L .