SQL statement that I can run to remove all image tags <img .... from MySQL column please.
Hi all, I'm after a SQL statement that I can run to remove all image tags and their content (<img .... ) from my column code please. I'm assuming it's going to be something regex'y but have no idea how to achieve this, or if its possible.
I'm using MySQL if that makes a difference
Thanks, Neil
MySQL ServerSQL
Last Comment
czuriaga
8/22/2022 - Mon
czuriaga
A small example of your column code, and the final desired result would be welcome
for example:
code="Hello this is an example <img blah blah blah> xxxxxx </img> bye bye"
code (fixed)="Hello this is an example bye bye"
but a real example.
Neil Thompson
ASKER
Thanks
Basically its every instance if <img and it's source any number of times, for example the following
<img width="400" height="122" title="transformation_logo_2.png" align="left" style="width: 400px; height: 122px;" alt="transformation_logo_2.png" src="/images/stories/transformation_logo_2.png" border="0" vspace="5" hspace="5" /><p>If you couldn't make the Transformation Drop-in Session this week don't worry You can catch up on the presentation <a name="file" title="HERE" href="/files/Drop in 270918.pdf" target="_self">HERE</a></p>
would become
<p>If you couldn't make the Transformation Drop-in Session this week don't worry You can catch up on the presentation <a name="file" title="HERE" href="/files/Drop in 270918.pdf" target="_self">HERE</a></p>
There may be multiple images per row and multiple attributes such as height,width, vspace etc within each image
czuriaga
Explore the REGEXP_REPLACE function, like this example...
mysql> set @code="<img width=""400"" height=""122"" title=""transformation_logo_2.png"" align=""left"" style=""width: 400px; height: 122px;"" alt=""transformation_logo_2.png"" src=""/images/stories/transformation_logo_2.png"" border=""0"" vspace=""5"" hspace=""5"" /><p>If you couldn't make the Transformation Drop-in Session this week don't worry You can catch up on the presentation <a name=""file"" title=""HERE"" href=""/files/Drop in 270918.pdf"" target=""_self"">HERE</a></p>";
Query OK, 0 rows affected (0,00 sec)
mysql> select @code\G
*************************** 1. row ***************************
@code: <img width="400" height="122" title="transformation_logo_2.png" align="left" style="width: 400px; height: 122px;" alt="transformation_logo_2.png" src="/images/stories/transformation_logo_2.png" border="0" vspace="5" hspace="5" /><p>If you couldn't make the Transformation Drop-in Session this week don't worry You can catch up on the presentation <a name="file" title="HERE" href="/files/Drop in 270918.pdf" target="_self">HERE</a></p>
1 row in set (0,00 sec)
mysql> select REGEXP_REPLACE(@code,"<img.*/>","");
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| REGEXP_REPLACE(@code,'<img.*/>',"") |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| <p>If you couldn't make the Transformation Drop-in Session this week don't worry You can catch up on the presentation <a name="file" title="HERE" href="/files/Drop in 270918.pdf" target="_self">HERE</a></p> |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)
Your help has saved me hundreds of hours of internet surfing.
fblack61
czuriaga
Is not always working... must to be fixed
mysql> set @code2="Hello <img width=""400"" alt=""1.png"" src=""/1.png"" /> this is <img width=""400"" alt=""2.png"" src=""/2.png"" />an example<img width=""400"" alt=""3.png"" src=""/3.png"" /> bye";
Query OK, 0 rows affected (0,00 sec)
mysql> select @code2\G
*************************** 1. row ***************************
@code2: Hello <img width="400" alt="1.png" src="/1.png" /> this is <img width="400" alt="2.png" src="/2.png" />an example<img width="400" alt="3.png" src="/3.png" /> bye
1 row in set (0,00 sec)
mysql> select REGEXP_REPLACE(@code2,"<img.*/>","");
+--------------------------------------+
| REGEXP_REPLACE(@code2,"<img.*/>","") |
+--------------------------------------+
| Hello bye |
+--------------------------------------+
1 row in set (0,00 sec)
Neil Thompson
ASKER
Thanks, I've tried that and I get this
UPDATE wp_postsSET post_content = REGEXP_REPLACE(post_content,"<img.*/>","")WHERE id = 265;
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
for example:
code="Hello this is an example <img blah blah blah> xxxxxx </img> bye bye"
code (fixed)="Hello this is an example bye bye"
but a real example.