Hello everyone, I am trying to figure out how I can edit a section of multiple posts.
When I converted from SMF>php>mybb this is what happens to posts that had links in them previously.
<!-- m --><a class="postlink" href="http://youtu.be/nq2jY1trxqg">http://youtu.be/nq2jY1trxqg</a><!-- m -->
<!-- m --><a class="postlink" href="http://community.mybb.com">http://community.mybb.com</a><!-- m -->
Is there an easy way to fix this with an SQL command? I want to remove everything except for the link that was within the href="".
<!-- m --><a class="postlink" href="Keep Me">Remove Me</a><!-- m -->
Not tested but yea I wish mysql had a regex replacing function built in
UPDATE mybb_posts SET message = REPLACE(REPLACE(message, '<!-- m --><a class="postlink" href="', ''), '</a><!-- m -->', '');
Always make a backup with messing with these sort of queries. You can VERY easily delete all of your posts with an incorrectly-formatted replace.
(2014-03-26, 09:55 AM)Rakes Wrote: [ -> ]Not tested but yea I wish mysql had a regex replacing function built in
UPDATE mybb_posts SET message = REPLACE(REPLACE(message, '<!-- m --><a class="postlink" href="', ''), '</a><!-- m -->', '');
Yes, I also wish there was a built in regex function. It would make things MUCH easier.
The below was very close.
This:
<!-- m --><a class="postlink" href="http://youtu.be/nq2jY1trxqg">http://youtu.be/nq2jY1trxqg</a><!-- m -->
Gets turned into this:
http://youtu.be/6tgf8n-J9rQ">http://youtu.be/6tgf8n-J9rQ
But it does nto remove the end part.
">http://youtu.be/6tgf8n-J9rQ
(2014-03-26, 06:14 PM)spork985 Wrote: [ -> ]Always make a backup with messing with these sort of queries. You can VERY easily delete all of your posts with an incorrectly-formatted replace.
Ha ha, don't worry, I ALWAYS backup when doing SQL edits.
(2014-03-26, 10:19 PM)thexshadow Wrote: [ -> ] (2014-03-26, 09:55 AM)Rakes Wrote: [ -> ]Not tested but yea I wish mysql had a regex replacing function built in
UPDATE mybb_posts SET message = REPLACE(REPLACE(message, '<!-- m --><a class="postlink" href="', ''), '</a><!-- m -->', '');
Yes, I also wish there was a built in regex function. It would make things MUCH easier.
The below was very close.
This:
<!-- m --><a class="postlink" href="http://youtu.be/nq2jY1trxqg">http://youtu.be/nq2jY1trxqg</a><!-- m -->
Gets turned into this:
http://youtu.be/6tgf8n-J9rQ">http://youtu.be/6tgf8n-J9rQ
But it does nto remove the end part.
">http://youtu.be/6tgf8n-J9rQ
(2014-03-26, 06:14 PM)spork985 Wrote: [ -> ]Always make a backup with messing with these sort of queries. You can VERY easily delete all of your posts with an incorrectly-formatted replace.
Ha ha, don't worry, I ALWAYS backup when doing SQL edits.
Dang forgot about that part, sorry.
DELIMITER $$
CREATE FUNCTION `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
DECLARE temp VARCHAR(1000);
DECLARE ch VARCHAR(1);
DECLARE i INT;
SET i = 1;
SET temp = '';
IF original REGEXP pattern THEN
loop_label: LOOP
IF i>CHAR_LENGTH(original) THEN
LEAVE loop_label;
END IF;
SET ch = SUBSTRING(original,i,1);
IF NOT ch REGEXP pattern THEN
SET temp = CONCAT(temp,ch);
ELSE
SET temp = CONCAT(temp,replacement);
END IF;
SET i=i+1;
END LOOP;
ELSE
SET temp = original;
END IF;
RETURN temp;
END$$
DELIMITER ;
UPDATE mybb_posts SET message = regex_replace('\">.*?<\/a><!-- m -->', '', regex_replace('<!-- m --><a class=\"postlink\" href=\"', '', message));
Else you need to dump -> edit -> import or use something that does extremely a lot of replaces manually
When i use the above I get this error.
#1139 - Got error 'repetition-operator operand invalid' from regexp
I know it is not liking the ? in it, but i do not know what to do.
And thanks for all this help.
---------------------------------------------------------------------------------------------------
Never mind, I just used notepad++ to replace everything. It was much easier.
I will just have to export the database when I do it for real, so its not that big of a deal.
And here is what i used if anyone comes across this and needs it.
Run these two separately.
ctrl+h and input the below. Leave the replace with box empty.
<!-- m --><a class="postlink" href="
ctrl+h and input the below. Leave the replace with box empty.
">.*?</a><!-- m -->
If done correctly, it should go from this:
<!-- m --><a class="postlink" href="http://community.mybb.com">http://community.mybb.com</a><!-- m -->
To this:
http://community.mybb.com
Yup, that works when you have a smaller database ( I wish I had one ;n; )
Btw, you could have done
<!-- m --><a class=\"postlink\" href=\"(.*?)\">.*?<\/a><!-- m -->
And replaced with
\1
As then you're selecting the href text and nothing else thus removing the rest of it out doing two replaces