I am carrying out a favor for any friend, getting him from Blogger and onto a located WordPress blog.

The large issue is, with more than 1,800 posts, you will find lots of image links to cope with. WordPress doesn't have mechanism to import these instantly, so I am doing the work by hand.

I have used wget to download each and every image which has have you been linked/embedded on the website. Now I want outside assistance creating a MySQL query to alter all the images within the blog for their new address.

For instance:


Must become:


Therefore the condition is, if your string in publish_content finishes in jpeg, digital, presen or png, replace:




I understand how to perform a blanket replace

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

But I am getting a difficult time determining how you can accomplish my more nuanced, conditional approach.

Thank you for any items you can provide. (Torn between posting here or ServerFault but SO appears like it's lots of MySQL gurus, here I'm.)

MySQL includes a great choice of string manipulation functions that you could plug to 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');

Whether it were me, though, I'd do two additional things: convert it to lowercase to complement e.g. '.JPG', and match the us dot before digital, presen, etc.:

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

REPLACE is only going to perform a change when the old substring is located - I do not begin to see the concern.

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

...works. If you wish to limit the updates to rows that contains "jpeg", "digital", "presen" 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


I do not think it may be completed in an easy query, but it is relatively simple related to an easy php script. simply have an easy loop in php to talk about each and every row with content and perform a preg_replace around the content area, then update that single row.

It isn't as elegant as doing the work in sql, nevertheless its certain to complete the job today as oposed to sometime this season.

P.S. this really is presuming there's more content than simply the URL, by which situation normal mysql string functions would suffice.

If everything fails, how about while using import feature? Then make use of a plugin to obtain your images too (look for your comments ought to within the wordpress plugin publish since you will find some relevant information).