Home / server / Questions / 220087
Accepted
javipas
Asked: 2011-01-09 02:37:13 +0800 CST

MySQL/Apache: Replace spaces with underscores only in certain URLs

  • 772

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

mysql apache-2.2 wordpress replace

2 Answers

  1. Best Answer

    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.

    • 1

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

    • 0