Remove "%" from strings in serialized arrays in MySQL database

In the course of converting a blog from Blogger to WP and running a script to grab hot-linked images for hosting, I ended up with some funky images names like

act%252Bapandas-210x290.png

These image names prevent the image from displaying on a webpage, due the url encoding ending up in the file name itself (don't ask!). I renamed them on the file server, no prob, but the names are also in the attachment metadata for each post.

How can I remove the "%" from all the image references in the wp_postmeta table? Most of them occur in serialized arrays in meta_values for the meta_keys of _wp_attachment_metadata. I've had no luck finding a plugin, and am unsure how to institute a pure SQL solution.

EDIT:

As the commentators have said, the issue is changing or removing the "%" character AND updating the array so it reports the correct number of characters (ie the s:13 would indicate yoursite.com is 13 char[]) I'm also open to using a php solution! Whatever can help me fix this mess.

AFTERNOTE AND SOLUTION

I didn't really frame this question in terms of WordPress issue, I framed it as a SQL problem. I awarded my answer accordingly. But I was able to solve the problem natively (with help from Rarst @.Here is how I solved the problem, using native WordPress functions inside the loop:

$posts = get_posts(array(
    'post_type' => 'attachment',
    'numberposts' => -1, ));

foreach( $posts as $post ) {

    // retrieve data, unserialized automatically
    $meta = get_post_meta($post->ID,'_wp_attachment_metadata', true);

    // loop thro开发者_运维知识库ugh array to do any search and replaces

    // write it back
    update_post_meta($post->ID, '_wp_attachment_metadata', $meta); }

These functions will automatically determine type of data you are retrieving or writing and (un)serialize it if necessary. This is handled by internal use of maybe_serialize() and maybe_unserialize() functions.

I'm sure it is not possible to do with pure SQL due to complexity of serialized array structure.

To get rid of the % signs in a column, you should be able to do something like:

UPDATE table_name
SET field_name = replace(field_name, '[%]', '')

If you just want to get rid of %'s, it is simple -- just replace '%' with something else with the REPLACE function.

However, I suspect you don't want this. It is not clear in your question, but I suspect that you'll want to decode that %xxx back to the correct character as well. In this case, you can first extract the "xxx" part of the string (which should be in hex) attach "0x" in front of it and cast it back to a number, then convert that number into string. You then use SUBSTRING to stitch back your text data, with the "%" removed and the new string substituted in-place.

EDIT: DO NOT DO THIS: THIS MAY BREAK THE SERIALIZED ARRAY (SEE COMMENT BELOW)

I'm keeping this (incorrect) answer just so that people will know its pitfalls.

Try this query: (replace the FIELD_NAMES with your field names entering them in as separate queries)

update wp_postmeta set FIELD_NAME = replace(FIELD_NAME, '%', '');

NOTE: You can change the '' and make it a space or whatever you want (leaving it as it replaces % with no space and nothing else).

EDIT: Nevermind, read the larger comment by Stephen Chung.

Here is how I solved the problem, using native WordPress functions inside the loop:

$posts = get_posts(array(
    'post_type' => 'attachment',
    'numberposts' => -1, ));

foreach( $posts as $post ) {

    // retrieve data, unserialized automatically
    $meta = get_post_meta($post->ID,'_wp_attachment_metadata', true);

    // loop through array to do any search and replaces

    // write it back
    update_post_meta($post->ID, '_wp_attachment_metadata', $meta); }