MySQL: Replace substring if string ends in jpg, gif or png

Question

Hi gang,

I'm doing a favor for a friend, getting him off of Blogger and onto a hosted WordPress blog.

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.

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.

For example:

http://www.externaldomain.com/some/link/to/an/image.jpg

Ought to become:

http://www.newbloghosting.com/wordpress/wp-content/uploads/legacy/www.externaldomain.com/some/link/to/an/image.jpg

So the condition is, if a string in post_content ends in jpeg, jpg, gif or png, replace:

http://

with

http://www.newbloghosting.com/wordpress/wp-content/uploads/legacy/

I know how to do a blanket replace with

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com');

But I'm having a hard time figuring out how to accomplish my more nuanced, conditional approach.

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.)

Answer 1

I don't think it can be done in a simple query, but it's relatively easy to do with a simple php script. just have a simple loop in php to go over every single row with content and do a preg_replace on the content field, then update that single row.

It's not nearly as elegant as doing it in sql, but its sure to get the job done today as oposed to sometime this year.

P.S. this is assuming there is more content than just the URL, in which case normal mysql string functions would suffice.

Answer 2

REPLACE will only perform an alteration if the old substring is found - I don't see the concern.

REPLACE(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com');

...will work. If you want to limit the updates to rows containing "jpeg", "jpg", "gif" and/or "png", add:

WHERE INSTR(post_content, 'jpeg') > 0
   OR INSTR(post_content, 'jpg') > 0
   OR INSTR(post_content, 'gif') > 0
   OR INSTR(post_content, 'png') > 0

References:

• REPLACE
• INSTR

Answer 3

MySQL has a great selection of string manipulation functions that you can plug into your query's WHERE section.

UPDATE wp_posts
SET post_content = REPLACE(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com')
WHERE RIGHT(post_content, 4) = 'jpeg'
   OR RIGHT(post_content, 3) IN ('jpg', 'gif', 'png');

If it were me, though, I'd do two additional things: convert it to lowercase to match e.g. '.JPG', and match the dot before jpg, gif, etc.:

WHERE LOWER(RIGHT(post_content, 5)) = '.jpeg'
   OR LOWER(RIGHT(post_content, 4)) IN ('.jpg', '.gif', '.png');

Answer 4

If everything fails, what about using the import feature? Then use a plugin to get your images as well (check for the comments in the plugin post since there are some relevant information).