Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Remove 'pid="%" dateline="%"' string from a quote tag using SQL

I’m converting my MyBB board to phpBB. The ONLY issue I’m having is that MyBB has quote tags like this:

[quote="Username" pid="4993" dateline="1703638754"]

But phpBB3 uses this:

[quote="Username"]

All I’d like to do is find any posts that contain the first format and change it into the second format within the database, then run the conversion again, which will fix the issue.

I tried this, but it only works if the post itself opens with a quote. Also if there are successive quote tags it doesn’t work on them

UPDATE mybb_posts
SET message = 
  CONCAT(
    '%[quote="',
    SUBSTRING_INDEX(SUBSTRING_INDEX(message, 'quote="', -1), '"', 1),
    '"]%',
    SUBSTRING_INDEX(message, '"]', -1)
  ) WHERE `message` LIKE '%[quote="%" pid="%" dateline="%"]%'
Tags:

2

Answers


  1. 0

    This query will be helpful for you.

    UPDATE mybb_posts
SET message = 
  CONCAT(
    '%[quote="',
    SUBSTRING_INDEX(SUBSTRING_INDEX(message, 'quote="', -1), '"', 1),
    '"]%',
    SUBSTRING_INDEX(message, '"', -1)
  )
WHERE message LIKE '%[quote="%" pid="%" dateline="%"]%'
    Login or Signup to reply.
  2. 0

    You could use regexp_replace like:

    UPDATE  mybb_posts
SET     message = REGEXP_REPLACE(message, 
            '" pid="[^"]*" dateline="[^"]*"]', 
            '"]')

    The [^"]* snippet means any character other than " occuring zero or more times.

    Example at DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search