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.
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
I'm using MySQL if that makes a difference
Thanks, Neil
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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
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
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
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_log
Query OK, 0 rows affected (0,00 sec)
mysql> select @code\G
**************************
@code: <img width="400" height="122" title="transformation_logo
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)
mysql> set @code="<img width=""400"" height=""122"" title=""transformation_log
Query OK, 0 rows affected (0,00 sec)
mysql> select @code\G
**************************
@code: <img width="400" height="122" title="transformation_logo
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
**************************
@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,"<im
+-------------------------
| REGEXP_REPLACE(@code2,"<im
+-------------------------
| Hello bye |
+-------------------------
1 row in set (0,00 sec)
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
**************************
@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,"<im
+-------------------------
| REGEXP_REPLACE(@code2,"<im
+-------------------------
| 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;
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial