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.
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%"
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)
