Removing links from posts in wordpress

Hi to all,
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 -> Test
<a href=”http:// dontneedlink.com”>Test Again -> Test Again
<a href=”http:// dontneedlink.com/childpage”>Test Again 2 -> 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.

Thanks in advance!

Below is a query I put together. It is super complicated, and someone likely will come along and replace it with a one liner, but it I believe does the trick based on the test case I put together based off your sample data.

WARNING: BACK UP YOUR DATA BEFORE YOU ATTEMPT TO DO ANYTHING WITH THIS.

Assumptions:

  1. Based on my understanding, post_content will be an entire post, not just a link. So we need to find the links within a chunk of text, and just replace the link portion leaving the rest of the text untouched.
  2. Links are in the form of text.
  3. The link text does not contain the greater-than or less-than characters (< or >). I.e. something like “This is < link text!” would break this.
  4. If there is more than one link in a single post_content that you want to replace, you’ll have to run the update more than once to catch the 2nd+ link.

Below is a SELECT of the sample data I used, the UPDATE I ran, and then another SELECT showing the resulting data. Note the “goodlink.com” link that is unaffected since it does not match the domain we are replacing.

Update query:


update wp_posts set post_content = replace(post_content, left(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('</a>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 3), left(mid(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 1, length(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)))), locate('<', mid(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 1, length(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))))) - 1)) where mid(post_content, locate('http://', post_content), length(post_content)) LIKE 'http://dontneedlink.com%';


mysql> select * from link_test;
+---------------------------------------------------------------------------+
| post_content |
+---------------------------------------------------------------------------+
| Text1 <a href="http://dontneedlink.com">Test</a> Text2 |
| Text 3 <a href="http://dontneedlink.com">Test Again</a> Text4 |
| Text 5 <a href="http://goodlink.com">Keep Me</a> Text 6 |
| Text 7 <a href="http://dontneedlink.com/childpage">Test Again 2</a>Text 8 |
+---------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> update link_test set post_content = replace(post_content, left(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('</a>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 3), left(mid(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 1, length(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)))), locate('<', mid(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 1, length(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))))) - 1)) where mid(post_content, locate('http://', post_content), length(post_content)) LIKE 'http://dontneedlink.com%';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from link_test;
+---------------------------------------------------------+
| post_content |
+---------------------------------------------------------+
| Text1 Test Text2 |
| Text 3 Test Again Text4 |
| Text 5 <a href="http://goodlink.com">Keep Me</a> Text 6 |
| Text 7 Test Again 2Text 8 |
+---------------------------------------------------------+
4 rows in set (0.00 sec)

mysql>

To do the replace for a different domain, just do a find/replace for “dontneedlink.com” and replace it with another domain in the same format.

I tested a few edge cases, but I expect you will run into exceptions. So again, make sure you back up your data.

Once you have a chance to test it let me know how it goes and I’ll try to help more if necessary. =)