New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
mysql experts ? how to do this
Hello ,
I have a WordPress site and i want to remove text from wp_posts table precisely in post_content field. Example of the text i want to remove :
<a href="http://www.mediafire.com"><img class="aligncenter wp-image-41174 size-full" src="https://www.ritavpn.com/blog/wp-content/uploads/2019/12/Best-APK-Download-Sites-for-2020.png" alt="" width="214" height="57" data-wp-pid="41174" /></a>
Note : http://www.mediafire.com and https://www.ritavpn.com/blog/wp-content/uploads/2019/12/Best-APK-Download-Sites-for-2020.png
are dynamic they are not the some links in all the posts !
So what I'm looking for is a query to remove the text that starts with
<a href=
and ends with
</a>
Any help will be appreciated !
Comments
delete from wp_posts where post_content like '%<a href=%</a>%'/\<a href(.*?)\<\/a\>/gThis is the regex to select the text starting with
<a hrefand ending with</a>both inclusive . Sorry unable to help you on the SQL queryWhile this would work, it doesn't delete the data relating to that post in other tables. So you would be left with orphaned data scattered throughout the database.
Also this should go without saying, make a backup of your database before running any suggested query that changes the database.
No, that'll delete all links, not just the one they're after.
You could have a link at the start and then one way at the end of the post and it'd match.
If anything he should be doing:
Francisco
But OP said
My interpretation was delete anything with an
<a>tag, but I can see how other interpretations are possible.You could be very much correct, i guess he has the answer to both possibilities.
Francisco
export the sql, search and delete using regex with editor, import the sql
Let's assume so unless OP clarifies otherwise
Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.
Francisco
That exactly describes what happened when my girlfriend said I didn't communicate enough.
He wants to delete the text, not the entire post. If you are running MySQL 8, you can use REGEXP_REPLACE function otherwise it is probably easier to write a small script in your preferred programming language to do the replacement rather than using pure SQL.
(if you don't know what you're doing, just don't)
Eeek, I'd never be able to get up in the morning
Yes the best is using regexp_replace or programming.
this!
this!
if OP blindly ran the command from the first answer without a proper backup he's probably pretty much f*cked already by now... because his posts be gone instead of just the text.
You should use WP CLI
search-replaceto find and replace data from DB... doing direct edits can break serialized entries in the databaseWhat is it? I too need solution for this.
https://interconnectit.com/products/search-and-replace-for-wordpress-databases/ - I have used this script many times when moving Wordpress sites from a domain to other.
Also it can be a solution for your request - just be sure to read all the notes and take your time before making the final "Submit". Don't forget to make a backup before making any change!!!
just keep it simple
delete from wp_posts where post_content like '%ritavpn%'
no.
Hire someone from Fiverr to do it manually for you.
This will remove all the post_content text not just the part i want to remove !!
Exactly !
did you notice before or learned the hard way?
@Francisco i don't want to remove the full text only a part of it and the links are dynamic , so i can't use your way
Well first i know this query will remove all the text not just a part of it ,
Second i always take a sql backup before doing any queries just in case !
Would this help? https://www.w3schools.com/sql/func_mysql_replace.asp
EDIT:
https://stackoverflow.com/questions/14527859/sql-to-remove-partial-text-from-value
To summarize long-standing wisdom: regex is the wrong way to handle HTML. It is inherently brittle. I'm not saying it can't work in many cases but tokenizing is not what regex is designed to do. You'll forever run into cases your naive regex hadn't anticipated, to say nothing of multi-line HTML, etc.
Scripting languages such as perl, python, etc. provide libraries to handle this. Bigger DBs (SQL Server, Oracle, etc.) include native support for parsing various markups (html, xml, json, etc.). I don't think MySQL has support for parsing HTML.
Are you doing this live? i.e., do you have a dataset where you can run a transform and then you're done, or is this query part of the web page? Doing it once is one thing because you can always check and you can do it in stages...e.g., create a new column that has the results from a simple step, then another column that has results from another step, then repopulate the first column with the results of another step, etc. until you have the process down. But that won't work if you're constantly adding new data in this format and expecting your web site to parse it on the fly.
So in other words it can't be done , or at least no one here knows how ?
of course it can be done. it's just no one will spoon-feed you ;-)
as other pointed out, I wouldn't necessarily try to do this directly at the database level though.
here are few more ideas how to approach that: https://www.cminds.com/5-best-search-replace-wordpress-tools-fix-content-website/