CODEWITHSUNDEEP
mysqlsql
Heisenberg
Heisenberg

Reputation: 46

How to remove all <img> tag from column using a SQL query?

I have table name wp_posts and in post_content column there is a description of my posts (in HTML).

I would like to have a query that searches in post_content column and selects the tag starting with <img class="aligncenter and ending with this "> and remove whole tag.

This :

<div class="col-lg-6>
<a href="#"><img class="aligncenter size-full" src="https://example.com/Guardians-Alien-Hunter.jpg" alt="random" /></a>
</div>

Would become this:

<div class="col-lg-6>
<a href="#"></a>
</div>

My MYSQL version is 5.7.28.

Upvotes: 1

Views: 1172

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522292

If you are using MySQL 8+, then REGEXP_REPLACE provides one option:

UPDATE wp_posts
SET post_content = REGEXP_REPLACE(post_content, '<img.*?/>', '')
WHERE post_content LIKE '%<img%';

On earlier versions of MySQL which do not support REGEXP_REPLACE, we can try using the base string functions:

UPDATE wp_posts
SET post_content = CONCAT(
    LEFT(post_content, LOCATE('<img', post_content) - 1), 
    SUBSTRING(post_content, LOCATE('/>', post_content, LOCATE('<img', post_content) + 4) + 2))
WHERE post_content LIKE '%<img%';

But note that the above 5.7 version is much more error prone than the regex version for 8+. Actually, for editing HTML content which might be nested, even regex is not suitable, and you should be using a parser, probably outside your database.

Upvotes: 1

Related Questions