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

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.

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

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)

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)

Thanks, I've tried that and I get this

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

Select all Open in new window

Error Code: 1305. FUNCTION intranet.REGEXP_REPLACE does not exist