fario
asked on
I need to replace similar text in 4600+ rows
Dear experts i have 10000 posts that i'm manually editing, but i need a faster way.
The problem:
4600+ posts contain has a string that i need to be removed.
How am i proceeding at the moment:
in phpMyAdmin i run the query
And i get what you can see in this image
http://i.imgur.com/iNeu5.png
Then i edit one of these posts in order to copy-paste the string "Written by Author"
http://i.imgur.com/tEYXF.png
so i can create for example the sql instruction
and run it in the hope that i can remove as more text strings as possible (each author usually wrote 1 to 50 posts). if i'm lucky i can remove 50 text strings in a sql run, else just 7 like in this case (that's frustrating)
http://i.imgur.com/Mo6g5.png
Luckily the content of all posts has the same structure: after the name of the author i find the text
What i need:
A way to obtain a sql query containing all the instructions ready to be run like this (hand made) example:
Thank you for reading
The problem:
4600+ posts contain has a string that i need to be removed.
How am i proceeding at the moment:
in phpMyAdmin i run the query
SELECT post_content FROM wp_posts WHERE post_content like '%. Written by %'
And i get what you can see in this image
http://i.imgur.com/iNeu5.png
Then i edit one of these posts in order to copy-paste the string "Written by Author"
http://i.imgur.com/tEYXF.png
so i can create for example the sql instruction
update wp_posts set post_content = replace(post_content,' Written by Alfred Jingle','');
and run it in the hope that i can remove as more text strings as possible (each author usually wrote 1 to 50 posts). if i'm lucky i can remove 50 text strings in a sql run, else just 7 like in this case (that's frustrating)
http://i.imgur.com/Mo6g5.png
Luckily the content of all posts has the same structure: after the name of the author i find the text
"</span></p><p>
so i think that there could be a way to find the name of all authors and create a query to remove them.What i need:
A way to obtain a sql query containing all the instructions ready to be run like this (hand made) example:
update wp_posts set `post_content` = replace(post_content,' Written by Alfred Jingle','');
update wp_posts set `post_content` = replace(post_content,' Written by Steve Rahaman','');
update wp_posts set `post_content` = replace(post_content,' Written by Corey Hatch','');
update wp_posts set `post_content` = replace(post_content,' Written by Huggo','');
update wp_posts set `post_content` = replace(post_content,' Written by Leo <mleonar1@ic3.ithaca.edu>','');
update wp_posts set `post_content` = replace(post_content,' Written by Tad Dibbern <DIBBERN_D@a1.mscf.upenn.edu>','');
update wp_posts set `post_content` = replace(post_content,' Written by <jhailey@hotmail.com>','');
update wp_posts set `post_content` = replace(post_content,' Written by Steve Richer <sricher@sympatico.ca>','');
update wp_posts set `post_content` = replace(post_content,' Written by Murray Chapman <muzzle@cs.uq.oz.au>','');
update wp_posts set `post_content` = replace(post_content,' Written by Anonymous','');
update wp_posts set `post_content` = replace(post_content,' Written by Abatoli Maksimov','');
update wp_posts set `post_content` = replace(post_content,' Written by Orange','');
update wp_posts set `post_content` = replace(post_content,' Written by Ed Sutton <esutton@mindspring.com>','');
update wp_posts set `post_content` = replace(post_content,' Written by Jim Beaver <jumblejim@prodigy.net>','');
update wp_posts set `post_content` = replace(post_content,' Written by John Vogel <jlvogel@comcast.net>','');
update wp_posts set `post_content` = replace(post_content,' Written by FilmFanUK','');
update wp_posts set `post_content` = replace(post_content,' Written by Film_Fan','');
update wp_posts set `post_content` = replace(post_content,' Written by Sujit R. Varma','');
update wp_posts set `post_content` = replace(post_content,' Written by Sujit R. Varma','');
update wp_posts set `post_content` = replace(post_content,' Written by Anthony Pereyra {hypersonic91@yahoo.com}','');
update wp_posts set `post_content` = replace(post_content,' Written by KGF Vissers','');
update wp_posts set `post_content` = replace(post_content,' Written by Jes Beard <jesbeard@bellsouth.net>','');
update wp_posts set `post_content` = replace(post_content,' Written by anonymous','');
update wp_posts set `post_content` = replace(post_content,' Written by IMDb Editors','');
update wp_posts set `post_content` = replace(post_content,' Written by Ryan McIntosh <Save_Ferris85@hotmail.com>','');
update wp_posts set `post_content` = replace(post_content,' Written by Sean Daly','');
update wp_posts set `post_content` = replace(post_content,' Written by matt-282','');
update wp_posts set `post_content` = replace(post_content,' Written by Grand Ilusions','');
update wp_posts set `post_content` = replace(post_content,' Written by Jonathan Ruskin <JonRuskin@aol.com>','');
update wp_posts set `post_content` = replace(post_content,' Written by PhatBleek','');
update wp_posts set `post_content` = replace(post_content,' Written by Julian Reischl <julianreischl@mac.com>','');
update wp_posts set `post_content` = replace(post_content,' Written by Jeff Hole <jeffhole@aol.com>','');
update wp_posts set `post_content` = replace(post_content,' Written by Michael Feller <reb@magna.com.au>','');
update wp_posts set `post_content` = replace(post_content,' Written by Tim Kretschmann <Tim.K@VirComm.com>','');
update wp_posts set `post_content` = replace(post_content,' Written by Tony B','');
update wp_posts set `post_content` = replace(post_content,' Written by Stephen Currence <billyc@erols.com>','');
update wp_posts set `post_content` = replace(post_content,' Written by wb','');
update wp_posts set `post_content` = replace(post_content,' Written by MGM Studios','');
Thank you for reading
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'VE Tried developing an sql statement to help you with your update unfortunately i don't have a mysql database available and worked with oracle one.
I'm not sure if the fuctions have the same correspondence in mysql but i'm sure that if they don't have the same name , they exist in some kind.
here's what i've came to till now, maybe it will serve as an example.
and here is the code i've used:
Again this is tested under oracle SQL.
Have a nice day.
sql-example.JPG
I'm not sure if the fuctions have the same correspondence in mysql but i'm sure that if they don't have the same name , they exist in some kind.
here's what i've came to till now, maybe it will serve as an example.
and here is the code i've used:
SELECT REPLACE (
text,
(SUBSTR (
text,
INSTR (text, 'Written by'),
(INSTR (text, '"</span></p><p>'))
- (INSTR (text, 'Written by'))
)),
'THIS TO BE REPLACED'
)
FROM POST
Again this is tested under oracle SQL.
Have a nice day.
sql-example.JPG
It's the double quote. Change line 34 from
$query = "Select * from wp_posts where post_content REGEXP '.*Written by [^"]+.*' LIMIT 100";
to
$query = "Select * from wp_posts where post_content REGEXP '.*Written by [^\"]+.*' LIMIT 100";
$query = "Select * from wp_posts where post_content REGEXP '.*Written by [^"]+.*' LIMIT 100";
to
$query = "Select * from wp_posts where post_content REGEXP '.*Written by [^\"]+.*' LIMIT 100";
i'd like to add thet the example i've provided will not work for all possibilities, i guess that the "written by part is in most cases at the end of the article -as per your example- so then we begin searching from the end and should only update the last "written by bla bla bla "</span></p><p>" in your table :
The update code should be:
IMPORTANT: please test -back up your table / database- this before you run it
sql-example.JPG
SELECT REPLACE (
post_content,
(SUBSTR (
post_content,
INSTR (post_content, 'Written by',-1),
(INSTR (post_content, '"</span></p><p>',-1))
- (INSTR (post_content, 'Written by',-1))
)),
''
)
FROM wp_posts
The update code should be:
update wp_posts set post_content=( REPLACE (
post_content,
(SUBSTR (
post_content,
INSTR (post_content, 'Written by',-1),
(INSTR (post_content, '"</span></p><p>',-1))
- (INSTR (post_content, 'Written by',-1))
)),
''
))
Done some research on google and mysql's substr is equivalent to "substring()"IMPORTANT: please test -back up your table / database- this before you run it
sql-example.JPG
ASKER
#1305 - FUNCTION watchon3_wrdp1.SUBSTR does not exist
removing the spaces after the SUBSTR and INSTR i get
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1)) - (INSTR(post_content, '. Written by',-1)) )), ''))' at line 1
removing the spaces after the SUBSTR and INSTR i get
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1)) - (INSTR(post_content, '. Written by',-1)) )), ''))' at line 1
ASKER
Forget about the automatic update of the DB
How can i use the REGEXP and my Query
to simply list all the author name ?
How can i use the REGEXP and my Query
SELECT post_content FROM wp_posts WHERE post_content like '%. Written by %'
to simply list all the author name ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It's not an accurate solution, but helped thanks
ASKER
This is my code
Open in new window