Replace string with wildcard

Question

I am moving a site that I manage from a custom CMS to Wordpress and am finding that some of the attributes on image tags to be problematic on display once in the WP environment. To fix this, I need to strip out the height attribute that is inlined into each image tag in the post_content column of the wp_posts table.

Starting with the original value in the DB, I want the following:

<img src="http://example.com/img/20150823_image.jpg" style="width: 730px; height: 730px;" />

To become:

<img src="http://example.com/img/20150823_image.jpg" style="width: 730px;" />

So, essentially, I need to trim out the " height: 730px;" portion. It is image-specific, so in this case it is 730 but in another it could be 1500, 447, 80, etc.

I was trying to see if I could use a '%' as a wildcard but that doesn't seem to be working...

UPDATE wp_posts SET post_content = REPLACE(post_content,' height: %px;','');

Any help would be greatly appreciated as I'd rather not have to manually go through thousands of rows stripping these out.

Answer 1

You can use a function to do the text parsing:

create function f_strip_height( in_s text ) returns text
begin

declare v_start int;
declare v_end int;
declare v_height text;

select locate(' height:', in_s ) into v_start;
if (v_start>0) then

  select locate( 'px;', substring( in_s, v_start)  ) into v_end;

  select trim(substring(substring( in_s, v_start, v_end+2), 9)) into v_height;

  if (v_end>0 and concat(cast(v_height as unsigned), 'px;' =  v_height)) then
    return concat(substring( in_s, 1, v_start-1), substring( in_s, v_start+v_end+2));
  end if;
end if;

return in_s;
end

Then use the function:

UPDATE wp_posts SET post_content = f_strip_height(post_content);

Answer 2

This is not a job for SQL. Here's a simple (?) PHP script that should do the trick, though I'm doing this off the top of my head so no guarantees:

<?php
// create the DB connection
$db = new PDO("mysql:host=localhost;dbname=wordpress", "user", "password");
// quiet warnings
libxml_use_internal_errors(true);
// prepare the update statement for later
$stmt = $db->prepare("UPDATE wp_posts SET post_content = ? WHERE post_id = ?");
// select the posts that at least have the word "height:" in them
$posts = $db->query("SELECT post_id, post_content FROM wp_posts WHERE post_content LIKE '%height:%'");
// loop through the posts
while ($post = $posts->fetch(PDO::FETCH_ASSOC)) {
    // create a DOM document
    $dom = new DomDocument();
    // load the HTML into the DOM parser
    $dom->loadHTML($post["post_content"], LIBXML_HTML_NOIMPLIED | LIBXML_HTML_NODEFDTD);
    // prepare the XPath
    $xpath = new DomXPath($dom);
    // get all img elements with a style attribute containing the word height
    $imgs = $xpath->query("//img[contains(@style, 'height')]");
    foreach ($imgs as $img) {
        // get the style attribute value
        $style = $img->getAttribute("style");
        // remove height
        $style = preg_replace("/height\s*:\s*\d+(px)?;?/", "", $style);
        // replace the attribute value
        $img->setAttribute("style", $style);
    }
    // output the new HTML
    $newhtml = $dom->saveHTML();
    echo "Updating post $post["post_id"] with new content:\n$newhtml\n\n";
    // save it into the database -- uncomment this line when you trust the script!
//    $stmt->execute([$newhtml, $post["post_id"]]);
}