Replace string with wildcard
You can use a function to do the text parsing:
create function f_strip_height( in_s text ) returns textbegindeclare 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;endThen use the function:
UPDATE wp_posts SET post_content = f_strip_height(post_content);
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 warningslibxml_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 postswhile ($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"]]);}
If you have the appropriate permission, you can use a UDF 27.4.2 Adding a New User-Defined Function, some can be:
In another case, as already mentioned, you can do your own function, here a version that you can modify and adjust as needed:
mysql> DROP TABLE IF EXISTS `wp_posts`;Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE IF NOT EXISTS `wp_posts` ( -> `post_content` TEXT -> );Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO `wp_posts` -> (`post_content`) -> VALUES -> ('<img src="http://example.com/img/20150823_image.jpg" style="width: 730px; height: 730px;" />'), -> ('<img src="http://example.com/img/20150824_image.jpg" style="width: 730px; height: 1500px;" />'), -> ('<img src="http://example.com/img/20150825_image.jpg" style="width: 730px; height: 80px;" />'), -> ('<img src="http://example.com/img/20150826_image.jpg" style="width: 730px; height: 0px;" />'), -> ('<img src="http://example.com/img/20150827_image.jpg" style="width: 730px;" />');Query OK, 5 rows affected (0.01 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> DELIMITER //mysql> DROP FUNCTION IF EXISTS `get_string`//Query OK, 0 rows affected (0.00 sec)mysql> CREATE FUNCTION `get_string`(`_string` TEXT, -> `_begin` VARCHAR(255), -> `_end` VARCHAR(255)) -> RETURNS TEXT DETERMINISTIC -> BEGIN -> DECLARE `_begin_pos` INT UNSIGNED DEFAULT LOCATE(`_begin`, `_string`); -> DECLARE `_end_pos` INT UNSIGNED DEFAULT 0; -> IF `_begin_pos` IS NOT NULL AND `_begin_pos` > 0 THEN -> SET `_end_pos` := LOCATE(`_end`, `_string`, `_begin_pos`); -> IF `_end_pos` IS NOT NULL AND `_end_pos` > 0 THEN -> RETURN SUBSTRING(`_string`, -> `_begin_pos`, -> (`_end_pos` + CHAR_LENGTH(`_end`)) - `_begin_pos`); -> END IF; -> END IF; -> RETURN ''; -> END//Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> SELECT `post_content` -> FROM `wp_posts`;+-----------------------------------------------------------------------------------------------+| post_content |+-----------------------------------------------------------------------------------------------+| <img src="http://example.com/img/20150823_image.jpg" style="width: 730px; height: 730px;" /> || <img src="http://example.com/img/20150824_image.jpg" style="width: 730px; height: 1500px;" /> || <img src="http://example.com/img/20150825_image.jpg" style="width: 730px; height: 80px;" /> || <img src="http://example.com/img/20150826_image.jpg" style="width: 730px; height: 0px;" /> || <img src="http://example.com/img/20150827_image.jpg" style="width: 730px;" /> |+-----------------------------------------------------------------------------------------------+5 rows in set (0.00 sec)mysql> UPDATE `wp_posts` -> SET `post_content` = REPLACE(`post_content`, `get_string`(`post_content`, ' height:', ';'), '');Query OK, 4 rows affected (0.01 sec)Rows matched: 5 Changed: 4 Warnings: 0mysql> SELECT `post_content` -> FROM `wp_posts`;+-------------------------------------------------------------------------------+| post_content |+-------------------------------------------------------------------------------+| <img src="http://example.com/img/20150823_image.jpg" style="width: 730px;" /> || <img src="http://example.com/img/20150824_image.jpg" style="width: 730px;" /> || <img src="http://example.com/img/20150825_image.jpg" style="width: 730px;" /> || <img src="http://example.com/img/20150826_image.jpg" style="width: 730px;" /> || <img src="http://example.com/img/20150827_image.jpg" style="width: 730px;" /> |+-------------------------------------------------------------------------------+5 rows in set (0.00 sec)Example in Rextester.