# Fix quotes after phpBB import

**URL:** <https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133>\
**Category:** Product Support\
**Created:** [January 27, 2018, 1:39am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133 "2018-01-27T01:39:46Z")\
**Posts on this page:** 1\
**Showing post:** 50

<div class="post-metadata">

**Author:** ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)\
**Post date:** [January 31, 2018, 12:56am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/50 "2018-01-31T00:56:18Z")

</div>

I’m certainly glad I tested this. From how I understood the documentation newlines needed to be double escaped. Not so. They needed to be only single escapes else they were written as literal text. I am fairly certain there are better ways to know the number of affected rows, but I do not know how to do them. Anyway, it looks good to me. a couple before and afters:

 ![newline1](https://global.discourse-cdn.com/meta/original/3X/f/f/ff91236f0e23dddda8133897c96c44ff671d3df3.png) ![newline2](https://global.discourse-cdn.com/meta/original/3X/9/0/90f0246e3864a855d1fbf672150683863204a924.png) ![newline3](https://global.discourse-cdn.com/meta/original/3X/4/3/43f07c915157323bdc3b1d92fd7d2fdd13c6336a.png) ![newline4](https://global.discourse-cdn.com/meta/original/3X/1/c/1cc311551e618e2d80a6925975773e67609cb65f.png)

To recap caveats for those that might be interested in this query

- It does not match against the lack of newlines around improperly formatted bbcode tags in raw post content
- It relies on Discourse displaying the bbcode tags in the cooked content to identify problem posts
- it does not take into account any posts where displaying of bbcode tags in the cooked content is desired (eg. in code fences)
- it matches per post, not per bbcode tag. Therefore if a post contains both improper and proper syntax bbcode tags, all will be surrounded by newlines
- it does not create edit revision histories
- posts require a rebake for the changes to be effected in cooked
- it can not be stressed strongly enough, **Save a backup _before_ running this query**
- the `RETURNING raw` line will show the number of affected rows _after_ outputting all of the raw content. Though acceptable with my small sample set, this may be undesireable for larger UPDATEs

```plaintext
WITH posts_with_bbcode_quotes AS (SELECT posts.id AS targeted_posts 
             FROM posts 
             WHERE posts.cooked ~* '\[quote[^]]*]'
             ORDER BY posts.id)
UPDATE posts 
SET raw = regexp_replace(posts.raw, '\[(/)?quote([^]]*)]', E'\n[\\1quote\\2]\n', 'g')
FROM posts_with_bbcode_quotes 
WHERE posts.id IN (posts_with_bbcode_quotes.targeted_posts) 
RETURNING raw;

```

---

_[View the full topic](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133)._