Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm having a problem with some images I'm using on my WordPress blog. After a migration I renamed every image replacing spaces with underscores, so

HIDDEN_264_4062_FOTO_IDF los MID.jpg

was renamed to

HIDDEN_264_4062_FOTO_IDF_los_MID.jpg

But althought the trick was necessary and worked for most of the posts, some of them try to find the old image, with spaces:

This is not found

http://www.example.com/files/HIDDEN_264_4062_FOTO_IDF%20los%20MID.jpg

and this should be the right URL

http://www.example.com/files/HIDDEN_264_4062_FOTO_IDF_los_MID.jpg

Careful, though, 'cause the "%20" is only shown on the browser: the text on the database shows spaces, not "%20".

I'd like to know if maybe I could make a SQL query in my WordPress MySQL database that replaces spaces in .jpg files with underscores. The path of the images is always the same, so the rule should transform this:

/files/HIDDEN_264_4062_FOTO_IDF los MID.jpg

/files/HIDDEN_264_4062_FOTO_IDF_los_MID.jpg

the "/files/HIDDEN_264_" part is always the same, but the rest varies. Is some way to perform this? Maybe a rewrite rule on Apache (our current webserver)?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

You can use mysql replace function like:

mysql> UPDATE your_table SET your_field = REPLACE(your_field, ' ', '_') WHERE your_field like '/files/HIDDEN_264_%';

This will replace all spaces with underscores in the file name saved in the field your_field.

In order to replace the spaces in part of your string, you can use substring function:

mysql> UPDATE your_table SET your_field = concat(REPLACE(substring(your_field from 1 for 30), ' ', '_'), substring(your_field from 31) WHERE your_field like '/files/HIDDEN_264_%';

The above example, replaces the spaces only in the first 30 characters in your_field. You can customise it according to the type of data you have. If you can not determine the right length (30 in this example), you can use locate function to search for specific pattern (delimiter).

I can not tell you more as I don't know the format of your data.

share|improve this answer
    
I'm affraid that's not entirely working. I've tried that and what I think it does is to replace EVERY SPACE on the post_content field, and I only want to replace the spaces on that images' links. In fact, there was a % missing at the beginning of the final "like" statement. What I've tried is this: UPDATE wp_posts SET post_content = REPLACE(post_content, ' ', '_') WHERE post_content like '%/files/HIDDEN_264_%'; But again, it's not entirely right :( Is there someway to restrict the replacement to the links? –  javipas Jan 8 '11 at 18:21
    
Yes, that's right. It will replace every space in your field. I don't know that it does contain other data in addition to the file name. –  Khaled Jan 8 '11 at 18:24
    
Yep, that's one of the main difficulties, I guess. The links I have to replace are just part of the articles (the HTML content of each article is in the wp_post table, in the post_content field), but I only need to replace the space on that links. Can it be done? –  javipas Jan 8 '11 at 19:25
    
Khaled, thank you so much for taking the time to try to solve this. I don't think the substring function could be the answer. Don't know if you know wordpress, but what i'm trying to do is, in every post, if there is a link with an image and its filename has a space, replace that with an underscore. So I'm not able to know where exactly is the link, and in fact, the same post (wp_posts->post-content) is an HTML text that can contain more than one image that has spaces, and all of them should be replaced with "_". What I need is to replace only the links, that follow that pattern HIDDEN... –  javipas Jan 9 '11 at 19:51

even if into the database the URL uses spaces when wordpress sends it to the browser will do URL encoding so it will convert spaces with their entity %20 you can try the SQL query above, but do a backup before mysqldump --all-databases > my-back.sql

share|improve this answer
    
Thanks for the comment silviud, I was going to do so anyway, but it's always great to remind someone these safe measures in case something goes wrong... –  javipas Jan 8 '11 at 18:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.