Fix quotes after phpBB import

Product Support
79

Hi,

Ok the easiest would be to break that into 2 separate replace like so:

UPDATE posts set raw = regexp_replace(raw, '\[member=(\d){0,4}]', '@', 'g') where raw like '%member=%';
UPDATE posts set raw = replace(raw, '[/member]', '') where raw like '%[/member]%';

Remember that sometimes depending on your enviroment you would have to escape special characters.

I would suggest you use a playground like this one to understand what is going on first:

https://regexr.com/

There is also a nice tutorial here:

For instance here is what I used for old emoticons that were still present on my board from old times:

update posts set raw = REPLACE(raw, 'B)', ' :slight_smile: ') where raw like '%B)%';
update posts set raw = REPLACE(raw, ':LOL:', ' :wink: ') where raw like '%:LOL:%';
update posts set raw = REPLACE(raw, ':wub:', ' :heart_eyes: ') where raw like '%:wub:%';
update posts set raw = REPLACE(raw, ':0', ' :open_mouth: ') where raw like '%:0%';
update posts set raw = REPLACE(raw, ':o', ' :open_mouth: ') where raw like '%:o%';

and some regex to clear up old user imports of whole html pages…

UPDATE posts set raw = regexp_replace(raw, '\[/?(size|font)=?“?"?\d{0,2}”?"?\]', '', 'g')                       where raw like '%size%' or raw like '%font%';
UPDATE posts set raw = regexp_replace(raw, '\[font=“?"?(\w+)(.(\w+))*“?"?\]', '', 'g')                          where raw like '%font=%';
UPDATE posts set raw = regexp_replace(raw, '\[/(font|color|background|center|i)\]', '', 'g')                    where raw like '%/font%' or raw like '%/color%' or raw like '%/background%' or raw like '%/center%' or raw like '%[/i%';
UPDATE posts set raw = regexp_replace(raw, '\[(center|i)\]', '', 'g')                                           where raw like '%[center%' or raw like '%[i%';
UPDATE posts set raw = regexp_replace(raw, '\[/?(left)\]', '', 'g')                                             where raw like '%left%';
UPDATE posts set raw = regexp_replace(raw, '\[/?(color="?#?).{0,8}"?\]', '', 'g')                               where raw like '%color=%';
UPDATE posts set raw = regexp_replace(raw, '\[(background=rgb).{10,15}\]', '', 'g')                             where raw like '%background=rgb%';

Hope it helps someone!