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

2 Views

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.

Read More

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

Post Views: 2

Related posts

Leave a Reply

4 comments

  1. 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');

  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:

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