# Fix quotes after phpBB import
**URL:** https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133
**Category:** Support
**Created:** [1월 27, 2018, 1:39오전 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133 "2018-01-27T01:39:46Z")
**Posts on this page:** 20
**Page:** 4
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [1월 31, 2018, 8:21오전 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/61 "2018-01-31T08:21:50Z")
> [@omarfilip](#):
>
> strings like ----Original message----, or Sent from my iPhone, etc.
> [@bekircem](#):
>
> multiple value expressions like [color=#] or [font=] tags
It should for the most part be possible to modify the regex
```plaintext
~* '\[quote[^]]*]'
.....
regexp_replace(posts.raw, '\[(/)?quote([^]]*)]', E'\n[\\1quote\\2]\n', 'g')
```
so that it can find (the ~\* case insensitive “LIKE” line) and replace (the table.field, pattern, replacement, flag line) characters and combinations of characters.
The trick is knowing exactly what to match so that it will both match what you want it to and _not_ match what you want it _not_ to. If the pattern is always and only the same characters it can be matched more easily. But maybe at times too easily eg. it is easy to match “cat” but you wouldn’t want to change “category” to “egory”.
As for trailing content a pattern something like ‘character string(.)\*’ (google “regex everything atom”) should work as long as the string doesn’t include any “special” characters.
As for the font and color bbcode tags, if these are in only raw and not causing a problem with the cooked, I wouldn’t bother worrying about them. If they are a problem, changing the string “quote” to “font” and then “color” and changing the replacement to an empty string should work if you only want to remove the tags entirely. If case is a concern, changing the flag to ‘gi’ _should_ work. _Test First!_
Documentation:
> **[9.7. Pattern Matching](https://www.postgresql.org/docs/9.3/functions-matching.html)**
>
> 9.7. Pattern Matching # 9.7.1. LIKE 9.7.2. SIMILAR TO Regular Expressions 9.7.3. POSIX Regular Expressions There are three separate approaches to …
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [1월 31, 2018, 7:06오후 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/62 "2018-01-31T19:06:51Z")
> [@Mittineague](#):
>
> WITH posts\_with\_bbcode\_quotes AS (SELECT posts.id AS targeted\_posts
Obviously, the “Sent from my iPhone” strings have nothing to do with `posts_with_bbcode_quotes`, so how should this be structured?
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [1월 31, 2018, 8:14오후 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/63 "2018-01-31T20:14:32Z")
The `posts_with_bbcode_quotes` is only an arbitrary name I gave it. It could just as easily have been something different. Some prefer shorter names such as “p”, but I prefer to use names that are more descriptive so I can easily know what they are and recognize them when I see them. In other words, there is no “magic” with that name.
For “sent by iphone” it could be named `posts_with_iphone_string` and the pattern could be simply the string "Sent from my iPhone”.
For simple string matches regex is probably overkill and
`LIKE '%Sent from my iPhone%'`
would be sufficient and a bit more efficient, but as this is hopefully only a one-time thing with only a relatively small number of posts I don’t think the difference would be significant.
Where regex will be needed however is in the replace since some text that is unknown will need to be “captured”
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [1월 31, 2018, 8:19오후 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/64 "2018-01-31T20:19:14Z")
Ah! I was looking for such a table in the Discourse docs. 😃
The regex for capturing the string and everything behind it is
`Sent from my iPhone\s*([^]*)`
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [1월 31, 2018, 8:24오후 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/65 "2018-01-31T20:24:21Z")
I don’t know where you found that pattern, but it looks off to me. It doesn’t error when you try it?
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [1월 31, 2018, 8:50오후 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/66 "2018-01-31T20:50:04Z")
See it here:
> **[RegExr: Learn, Build, & Test RegEx](https://regexr.com/3k2mg)**
>
> RegExr is an online tool to learn, build, & test Regular Expressions (RegEx / RegExp).
I have not worked it into [Data Explorer](https://meta.discourse.org/t/32566?silent=true) yet to test.
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [1월 31, 2018, 10:24오후 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/67 "2018-01-31T22:24:48Z")
There’s also this version:
`Sent from my iPhone[^]]+`
[https://regex101.com/r/oLTQYL/2](https://regex101.com/r/oLTQYL/2)
Edit: this one works, but I will need to add a stop at `![]()
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [1월 31, 2018, 11:19오후 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/68 "2018-01-31T23:19:21Z")
These last links are to code sharing sites? I get the impression you do not understand how regex works and are using one example that works in one situation and substituting bits and pieces in hopes it will work in a different situation. Going about it this way will lead to pain. Regex isn’t really a language where “trying different characters without understanding what they do in hopes it will work” is a good idea. It is much more a “trying different characters that I understand how they work and testing to make sure they do”.
As I posted previously it is very important, I’d say essential, that you know exactly what text you will be working with and exactly what you want to do with it. Making best guesses will rarely if ever work as hoped.
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [1월 31, 2018, 11:25오후 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/69 "2018-01-31T23:25:20Z")
They are Regex builder/testers, where I can use the actual content from the forum to test and tweak the expression.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2월 1, 2018, 12:17오전 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/70 "2018-02-01T00:17:25Z")
OK, here’s my translation for this pattern
`Sent from my iPhone[^]]+`
the string “Sent from my iPhone” followed by one or more characters that are not the closing square bracket character.
I used this for the bbcode quote tags because it was a relatively safe assumption that the next closing square bracket after the string `[quote` would be that specifying the end of the tag. Not a 100% safe assumption, a tag like `[quote hash="ft3fk]dr"]` would have broke and for something critically important that possibility would need to be addressed.
As for removing “this string and everything after it”, despite my aversion to using the “regex everything atom” because of the problems it can cause, in this case it is probably appropriate to use it. But if you do not want to remove “_everything_ after it” and want to keep certain things the pattern could get extremely complex. For example, this for “_most_” phone numbers
[https://www.sitepoint.com/community/t/phone-number-regular-expression-validation/2204/5](https://www.sitepoint.com/community/t/phone-number-regular-expression-validation/2204/5)
```plaintext
^((\+\d{1,3}(-| )?\(?\d\)?(-| )?\d{1,3})|(\(?\d{2,3}\)?))(-| )?(\d{3,4})(-| )?(\d{4})(( x| ext)\d{1,5}){0,1}$
```
Unless you do want to remove everything after the string “Sent from my iPhone” manually editing the posts individually, in spite of how unattractive that may seem, is likely to result in less time spent and less errors.
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [2월 1, 2018, 1:26오전 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/71 "2018-02-01T01:26:56Z")
That should have been a `<` instead of `]` to look for the start of `
### Author:  [@Tomasz136472](https://meta.discourse.org/u/Tomasz136472)
#### Post date: [2월 23, 2018, 6:41오후 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/72 "2018-02-23T18:41:56Z")
Hi @omarfilip,
I have completed my import from IPB recently with help of @pfaffman
I found, however, using rake commands to slow for fixing quotes and stuff as my forum has 1.3 mln posts it does take time.
I prefer to do all of the stuff directly on the database and then to rebake.
For the quotes for instance I would do:
```plaintext
UPDATE posts SET raw = REPLACE(raw, '[quote=', ''||chr(10)||'[quote=') where raw like '%[quote=%';
UPDATE posts SET raw = REPLACE(raw, '[/quote]', '[/quote]'||chr(10)||'') where raw like '%[/quote]%';
```
and then rebake.
I would actually fix everything I can before I even start rebake as you can see you changes live by simple regenerating HTML or editing the posts.
Hope it helps!
---
### Author:  [@cmwebdev](https://meta.discourse.org/u/cmwebdev)
#### Post date: [2월 25, 2018, 11:06오후 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/73 "2018-02-25T23:06:43Z")
This worked good for me on an SMF forum. I wish it detected whether line breaks were already there, but I can live with the extra line breaks.
There is a mention plugin installed on my SMF forum so there’s a bunch of bbcodes that look like this:
```plaintext
[member=4953]username[/member]
```
I think I just need to replace the `[member=4953]` part with the `@` symbol and then delete the `[/member]` closing tag. I couldn’t figure out how to modify the quote `regexp_replace` to work on these member mention tags.
Any help here would be great appreciated. Thanks!!!
---
### Author:  [@cmwebdev](https://meta.discourse.org/u/cmwebdev)
#### Post date: [2월 25, 2018, 11:34오후 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/74 "2018-02-25T23:34:57Z")
Don’t you need a line break before and after each opening or closing quote tag? I think your solution only adds one before the opening tag and after the closing tag, which gets you halfway there. For example, the closing tag would have to be:
`||chr(10)||[/quote]||chr(10)||`
Note: I was confused by the single quotes in the replace string so I left those out.
Also, what do you mean by doing it directly on the database? Weren’t they already doing the `UPDATE` directly on the database? Thanks.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2월 26, 2018, 12:07오전 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/75 "2018-02-26T00:07:25Z")
> [@cmwebdev](#):
>
> what do you mean by doing it directly on the database?
It’s a matter of what syntax you prefer.
The Ruby console using ActiveRecord syntax. Or the Postgres console using PostgreSQL syntax. They _both_ have the ability to modify the database. And they both have the ability to _break_ the database. IMHO this is something that should be _not_ experimented with on a live site, available backup or no.
I strongly recommend you set up a testing environment and work with that before doing anything with a live site’s database.
---
### Author:  [@cmwebdev](https://meta.discourse.org/u/cmwebdev)
#### Post date: [2월 26, 2018, 12:21오전 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/76 "2018-02-26T00:21:38Z")
Oh ya, I’m definitely doing all this on a Vagrant box. I am comfortable with SQL queries, I just am somewhat new to Ruby/Rails so I didn’t realize there was a different console you could use on the database besides `psql`.
Would really appreciate your help with the mentions tags I described a couple posts up. I was able to come up with this regex which matches the member tags:
```plaintext
\[(\/)?member([^]]*)]
```
As demonstrated here: [https://regexr.com/3lavf](https://regexr.com/3lavf)
It was giving me an error on RegExr if I didn’t escape the forward slash. Yours wasn’t escaped in the quote query so I guess it’s not necessary. Now I just need to figure out how to replace the opening tag with the @ symbol and delete the closing tag. Might need two separate queries to accomplish that.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2월 26, 2018, 3:12오전 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/77 "2018-02-26T03:12:34Z")
There are some differences between different “flavors” of regex, eg. PCRE vs. POSIX. Anyway, in this case, when inside of a character class, the closing square bracket does not need to be escaped as long as it is the first character in the class.
The `(\/)?` capture group is “zero or one” backslash and will match both opening and closing bbcode tags. If you want to treat opening and closing bbcode tags differently, (replace opening, remove closing) then I think two separate regex would be the simplest (if not necessary) way to do it.
---
### Author:  [@Tomasz136472](https://meta.discourse.org/u/Tomasz136472)
#### Post date: [2월 26, 2018, 11:21오전 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/78 "2018-02-26T11:21:09Z")
> [@cmwebdev](#):
>
> ||chr(10)||[/quote]||chr(10)||
I did not find this necessary. One before and one after did the trick.
The easiest way to experiment is as mentioned by @Mittineague create clone where you can break things, you can do that easily if you use something like digital ocean.
Then do an experiment on one post only. Go to its edit box and you will see straight away if you changes are taking expected effect.
I have to say that, for some reason, I had a hard time getting regex to work on my Postgres database. Maybe that is actually a problem with IDE - Datagrip from Jetbrains.
I’ll test it and post here when I get it to work.
---
### Author:  [@Tomasz136472](https://meta.discourse.org/u/Tomasz136472)
#### Post date: [2월 27, 2018, 12:40오전 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/79 "2018-02-27T00:40:17Z")
> [@cmwebdev](#):
>
> [member=4953]username[/member]
Hi,
Ok the easiest would be to break that into 2 separate replace like so:
```plaintext
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/](https://regexr.com/)
There is also a nice tutorial here:
> **[RegexOne - Learn Regular Expressions - Lesson 1: An Introduction, and the ABCs](https://regexone.com/lesson/introduction_abcs)**
>
> RegexOne provides a set of interactive lessons and exercises to help you learn regular expressions
For instance here is what I used for old emoticons that were still present on my board from old times:
```plaintext
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…
```plaintext
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!
---
### Author:  [@cmwebdev](https://meta.discourse.org/u/cmwebdev)
#### Post date: [2월 28, 2018, 3:15오후 UTC](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133/80 "2018-02-28T15:15:30Z")
Thanks for the regular expressions!
> [@Tomasz136472](#):
>
> I did not find this necessary. One before and one after did the trick.
To clarify, this is only true if the quote itself is one line. If there are any line breaks inside the quoted text (ie multiple paragraphs), then the opening and closing quote tags need to be on their own line. So a line break before and after each tag is necessary _some_ of the time.
[이전 페이지](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133.md?page=3)
[다음 페이지](https://meta.discourse.org/t/fix-quotes-after-phpbb-import/79133.md?page=5)