Removing links from posts in wordpress using query

Question

I am not very good with mysql so i need some help.

I am trying to remove some specific links from my wordpress blog. I have a lot of outgoing links to several domains and i want to remove just links to one of them. For example all links that lead to dontneedlink.com or start with dontneedlink.com/(some child page)

I already try

UPDATE wp_posts SET post_content = REPLACE (
post_content,
'Item to replace here',
Replacement text here');

But this is not god for me because I have a lot of key words and there is a lot of combinations.

So I need some kind of query to recognize my links that include dontneedlink.com and to remove href text and leave keyword untouched.

<  a href=”http:// dontneedlink.com”>Test</a> -> Test 

<  a href=”http:// dontneedlink.com”>Test Again</a> -> Test Again

<  a href=”http:// dontneedlink.com/childpage”>Test Again 2</a> -> Test Again 2

Is this possible at all? This would save me a lot of time instead to go from one to another post and remove manually.

Answer 1

you need to use 'like' to find the posts and substring index to remove the unwanted content

substring_index( substring_index(table.column, 'href=”', -1), '"',1) will extract your link

mysql> SELECT REPLACE(table_column, substring_index( substring_index(table.column, 'href=”', -1),  '"',1) , '');

this will give you the text without the link. You will be left with

<  a href=””>Test</a>

then do another replace or substring index to remove any undesired remains.

finally run it with the right filter in where:

UPDATE wp_posts SET post_content = REPLACE (
post_content,
'Item to replace here',
Replacement text here')
where mycolumn like "%dontneedthisdomain.com%"

Answer 2

To expand and simplify on @AdrianBR Run the following SQL...

UPDATE table_name SET column_name = REPLACE(column_name, substring_index( substring_index(column_name, 'href="', -1),  '"', 1),'');
UPDATE table_name SET column_name = REPLACE(column_name, '<a href="">','');
UPDATE table_name SET column_name = REPLACE(column_name, '<a href="" target="_blank">','');
UPDATE table_name SET column_name = REPLACE(column_name, '</a>','');

Life becomes tricky when class and other attributes are added to links, but the approach above should be tweak-able to cover these cases also (depending on your requirements)