# Fix quotes after phpBB import
**URL:** https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133
**Category:** 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:** 20
**Page:** 1
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 27, 2018, 1:39am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/1 "2018-01-27T01:39:46Z")
When I imported from phpBB, some quotes were not being handled correctly, as discussed [here](https://meta.discourse.org/t/importing-from-phpbb3/30810/289).
How can I fix them after the fact?
They currently look like this:
[quote]some text
[/quote]
and like this:
[quote=“John Doe”]some text
John[/quote]
The quote tags need to be on a new line.
I looked at this, but couldn’t figure out if it could be applied:
> [@Replace a string in all posts](https://meta.discourse.org/t/replace-a-string-in-all-posts/48729):
>
> bookmark This guide explains how to replace a string in all posts within a Discourse instance. person_raising_hand Required user level: System Administrator warning Console Access Required Want to replace a string in all the posts on a site? Let’s get started! warning WARNING: We strongly recommend you take a full backup before proceeding, and make sure your string replacement is specific enough to affect only the places you want it to. If this string replacement goes wrong, every…
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [January 27, 2018, 2:25am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/2 "2018-01-27T02:25:33Z")
Nothing personal, but if you don’t understand what’s in that topic then you probably shouldn’t experiment with regex. If you get it wrong it could result in anything form ineffective, incomplete or disastrous enough where you would need to restore your backup.
Using the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin try this query
```plaintext
SELECT COUNT(id)
FROM posts
WHERE cooked LIKE '%[quote%'
```
and see how many you’ll need to deal with.
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 27, 2018, 2:34am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/3 "2018-01-27T02:34:41Z")
I asked because:
[https://meta.discourse.org/t/replace-a-string-in-all-posts/48729/8?u=omarfilip](https://meta.discourse.org/t/replace-a-string-in-all-posts/48729/8)
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [January 27, 2018, 2:38am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/4 "2018-01-27T02:38:04Z")
Ah OK, you’re thinking rake task and I was concerned you might be tempted to run a command.
Anyway, what result do you get with the query?
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 27, 2018, 3:12am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/5 "2018-01-27T03:12:20Z")
12153 is the count from the query.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [January 27, 2018, 4:22am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/6 "2018-01-27T04:22:10Z")
Thanks, I was hoping it would be much less than that so that manually editing them individually wouldn’t be too tedious. But for that many one would need to have exceptional perseverance.
You will definitely want to save a backup before doing anything potentially destructive in the CLI. And it would be best to hone the pattern until the count value is the same before changing any content.
You didn’t show any in your example bbcode, so I didn’t think of it at the time, but you should run that query again using ILIKE (case insensitive) just in case you have any `[QUOTE]` tags. (hopefully not)
My ActiveRecord foo is less than I’d like it to be, but Inside the rails console try
```plaintext
Post.where("cooked REGEXP ?", '\[quote').count
```
If the results don’t match it will need some refinements.
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 27, 2018, 4:26am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/7 "2018-01-27T04:26:11Z")
Thanks!
The rails command generates this error:
```
[1] pry(main)> Post.where("cooked REGEXP ?", '\[quote').count
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "REGEXP"
LINE 1: ...WHERE ("posts"."deleted_at" IS NULL) AND (cooked REGEXP '\[...
```
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [January 27, 2018, 4:33am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/8 "2018-01-27T04:33:39Z")
I’m guessing and hoping it doesn’t like the backslash escape. Maybe?
```plaintext
Post.where("cooked REGEXP ?", '[quote').count
```
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 27, 2018, 4:37am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/9 "2018-01-27T04:37:38Z")
That wasn’t it, same error, unfortunately.
Edit: Syntax error ^ points at **R** EGEXP
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [January 27, 2018, 4:47am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/10 "2018-01-27T04:47:42Z")
Bah! I’ll try one more before I defer to someone more adept than I and start thinking the Postgres console which I’m more comfortable with instead if someone else doesn’t jump in.
```plaintext
Post.where("cooked REGEXP ?", /\[quote/).count
```
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 27, 2018, 4:52am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/11 "2018-01-27T04:52:49Z")
Sadly, no luck. I do appreciate the efforts, though!
`TypeError: can't quote Regexp`
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [January 27, 2018, 4:59am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/12 "2018-01-27T04:59:31Z")
And here I was thinking I was finally getting better with ActiveRecord in the rails console 🙁
Most likely a simple syntax error or maybe needs to use downcasing instead.
Anyway, if you can get to the pg console try
```plaintext
SELECT COUNT(id)
FROM posts
WHERE cooked ILIKE '%[quote%';
```
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 27, 2018, 5:14am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/13 "2018-01-27T05:14:59Z")
pg console: 12152
[data explorer](https://meta.discourse.org/t/32566?silent=true): 12148
[data explorer](https://meta.discourse.org/t/32566?silent=true) with uppercase QUOTE: 2
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [January 27, 2018, 5:28am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/14 "2018-01-27T05:28:50Z")
Thanks. I’m guessing the other two are Mixed Case.
I’m on my iPad now, but I’ll go to my desktop to look at my notes of queries that I know have worked for me and edit this post ASAP.
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 27, 2018, 5:31am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/15 "2018-01-27T05:31:42Z")
You are correct - Quote returns 2 in [data explorer](https://meta.discourse.org/t/32566?silent=true).
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [January 27, 2018, 6:13am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/16 "2018-01-27T06:13:27Z")
Found what I was looking for. POSIX regex is a bit different from LIKE or SIMILAR TO.but it allows for more finely tuned pattern matching. The result should be the same as the 12152 query
```plaintext
SELECT COUNT(DISTINCT(id))
FROM posts
WHERE cooked ~* '\[quote'
```
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 27, 2018, 6:22am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/17 "2018-01-27T06:22:52Z")
Yes, it’s the same: 12152.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [January 27, 2018, 6:41am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/18 "2018-01-27T06:41:22Z")
Great! Now comes the decision making as to what you want to happen. eg.
- `[quote]foobar[/quote]` to foobar
- `[quote some_attr="some_val"]foobar[/quote]` to foobar
- `[quote]foobar[/quote]` to `foobar
`
- `[quote some_attr="some_val"]foobar[/quote]` to `foobar
`
AFAIK the blockquote tags in Discourse do not have attributes and trying to keep them would likely be a poor idea. Would one of the first three be OK even with losing any attributes there might be?
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 27, 2018, 6:51am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/19 "2018-01-27T06:51:14Z")
I’d be perfectly happy with inserting a line break before and after [quote%] and a line break before and after [/quote].
Losing the attribute would be ok.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [January 27, 2018, 7:01am UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/20 "2018-01-27T07:01:40Z")
Well, except that once the bbcode tags are replaced there won’t be a before and after, that will make the query easier. So like this would be OK, are you sure?
- `[quote]foobar[/quote]` to `
foobar
`
- `[quote some_attr="some_val"]foobar[/quote]` to `
foobar
`
[Next page](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133.md?page=2)