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
LVL 3
Neil ThompsonSenior Systems DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

czuriagaCommented:
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.
0
Neil ThompsonSenior Systems DeveloperAuthor 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
0
czuriagaCommented:
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)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

czuriagaCommented:
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)
0
Neil ThompsonSenior Systems DeveloperAuthor 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
0
czuriagaCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.