title: MySQL: Replace substring if string ends in jpg, gif or png
body: <p>I'm doing a favor for a friend, getting him off of Blogger and onto a hosted WordPress blog.</p>
  
  <p>The big problem is, with over 1,800 posts, there are a lot of image links to deal with. WordPress has no mechanism to import these automatically, so I'm doing it manually.</p>
  
  <p>I've used wget to download every single image that has ever been linked/embedded on the site. Now I need some help building a MySQL query to change all of the images in the blog to their new address.</p>
  
  <p>For example:</p>
  
  <pre><code>http://www.externaldomain.com/some/link/to/an/image.jpg
  </code></pre>
  
  <p>Ought to become:</p>
  
  <pre><code>http://www.newbloghosting.com/wordpress/wp-content/uploads/legacy/www.externaldomain.com/some/link/to/an/image.jpg
  </code></pre>
  
  <p>So the condition is, if a string in post_content ends in jpeg, jpg, gif or png, replace:</p>
  
  <pre><code>http://
  </code></pre>
  
  <p>with</p>
  
  <pre><code>http://www.newbloghosting.com/wordpress/wp-content/uploads/legacy/
  </code></pre>
  
  <p>I know how to do a blanket replace with</p>
  
  <pre><code>UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com');
  </code></pre>
  
  <p>But I'm having a hard time figuring out how to accomplish my more nuanced, conditional approach.</p>
  
  <p>Thanks for any guidance you can offer. (Torn between posting here or ServerFault but SO looks like it has plenty of MySQL gurus, so here I am.)</p>