I want to delete all <a> tags in the string in the SQL regular expression.

Below is the data from the post_content column (type=LONGTEXT) in the wp_posts table.

target data:

<h1id="title1">h1</h2>


<a href="#title1">aaa</a>
<a href="#title2">bbb</a>
<a href="#title3">ccc</a>
<a href="#title4">ddd</a>
<a href="#title5">eee</a>

<h2id="title2">h2</h2>

I would like to remove all <a> tags with href="#title".Ideally, it should look like the following after the deletion.

after deletion:

<h1id="title1">h1</h2>

<h2id="title2">h2</h2>

I think it will be SQL like this, but I would like to add a <a> tag with href="#title".
I use MySQL 5.6

UPDATE
  wp_posts
SET
  post_content=REPLACE(post_content, '<a href="#title1">aaa</a>',') WHERE wp_posts.post_content LIKE'%<a href="#title1">aaaaa>%;;;;;;;

mysql sql regular-expression

2022-09-30 14:36

1 Answers

If the title part is assumed to be any string,

REGEXP_REPLACE(post_content, '<a href="#.*?">.*?</a>', ')

That's how it feels.

2022-09-30 14:36

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656