XF 2.1 Find/Replace Links in Post Table


I imported 2 vb4 forum DBs into my new XF DB.

domain1.com: (Retain content IDs ), was imported first more important forum
domain2.com : (sequential ID) will require redirector, not concerned with this data for now.

domain1 still uses the same domain after merger and is the more important DB so no need for re-directer.
I prefer to clean up the post table xf_post at field message

In the post table, there are 22 thousand entries of vb4 format of domain1.com/forums/showthread.php?

I want to mass edit my post table with a find/replace query but the thread label is complicating it.

I want the thread links to go from this


to this


If I replace

UPDATE xf_post SET message = REPLACE(message, 'domain1.com/forums/showthread.php?', 'domain1.com/forums/index.php?threads/.');

it almost fixes it but the label remains at the end and that breaks the XF format


For XF the label should be before the before thread ID or none at all. I figure there must be an easy way to rid of the trailing labels so the link looks like


some suggested using regex but it expands to a tree when sending the command, not sure what else its asking me for

REGEX_REPLACE( message , 'domain1.com/forums/showthread.php\\?([0-9]+)-[\w-]+' , 'domain1.com/forums/index.php?threads/.\\1&' )
Last edited: