We help IT Professionals succeed at work.

SQL statement that I can run to remove all image tags <img .... from MySQL column please.

Neil Thompson
Neil Thompson asked
on
6 Comments
1 Solution
299 Views
Last Modified: 2018-10-05
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
Comment
Watch Question

View Solution Only

czuriaga

Commented:
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 ThompsonSenior Systems Developer

Author

Commented:
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

Commented:
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)
czuriaga

Commented:
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 ThompsonSenior Systems Developer

Author

Commented:
Thanks, I've tried that and I get this

UPDATE wp_posts
SET post_content = REGEXP_REPLACE(post_content,"<img.*/>","")
WHERE id = 265;

Open in new window

Error Code: 1305. FUNCTION intranet.REGEXP_REPLACE does not exist
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION