RegEx for string replacement in MySQL Database

Question

I have recently upgrade an IPB version of my forum but the quotes were not upgraded and IPS is not giving the support I need.

I need to build a regular expression do find and replace

For example this is the old forum format:

<div class="quotetop">QUOTE(Cleber__v @ Apr 14 2015, 12:25 PM) <a href="index.php?act=findpost&pid=2778161"><{POST_SNAPBACK}> </a></div><div class="quotemain"> TEXT TO BE KEPT  </div>

And this is the new format it should be on:

`<div>
<blockquote class="ipsQuote" 
data-cite="Em 14/04/2015, (Cleber__v disse:" data-ipsquote="" 
data-ipsquote-timestamp="1428004301" data-ipsquote-userid="2350" 
data-ipsquote-username="Cleber__v" data-ipsquote-contapp="forums" 
data-ipsquote-contenttype="forums" data-ipsquote-contentclass="forums_Topic" 
data-ipsquote-contentid="105179" data-ipsquote-contentcommentid="2768819">
TEXT TO BE INSERTED</blockquote></div><p><span></span></p>`

So I need to find the content, save Username, postID (or commentid), time and text and replace it with the correct format.

I've been researching regex for about a week now with no sucess on how to make this happen

Anybody could help? Thank you

Maybe you need a xml parser. What's exactly you've done so far? — luoluo
– luoluo, Commented Sep 24, 2015 at 6:04

tchap · Accepted Answer · 2015-09-24 09:28:42Z

1

You should try for youself and give us the regex you tried, but here is a first step for you :

.*?>QUOTE\((?P<name>.*)\ \@\ (?P<date>[^\)]+).*?pid\=(?P<pid>[0-9]*).*?\<\!\-\-quotec\-\-\>(?P<text>.*?)\<\!\-\-QuoteEnd\-\-\>

See here how it works : https://regex101.com/r/dM0eG3/1 and how the match information corresponds to your need.

NB : you must remove all new line characters before applying this regex, but this is fairly easy to do in PHP or in any language you might use to create your db upgrade script.

That will extract all the relevant information from your text. Replacing these in the new format is left as an exercise for the reader.

answered Sep 24, 2015 at 9:28

tchap

3,4323 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hugo Palma Over a year ago

Will certainly do, thanks for the kickstart, thats exactly what i needed

Collectives™ on Stack Overflow

RegEx for string replacement in MySQL Database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related