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

Neil Thompson
Neil Thompson used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

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)
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

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:
it seems REGEXP_REPLACE is available in MySQL v8

https://dev.mysql.com/doc/refman/8.0/en/regexp.html

v5.7 and previous was not developed, and people built their custom stored procedures / functions to emulate it

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial