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 plugin try this query
SELECT COUNT(id)
FROM posts
WHERE cooked LIKE '%[quote%'
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
Post.where("cooked REGEXP ?", '\[quote').count
If the results don’t match it will need some refinements.
[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 '\[...
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.
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
SELECT COUNT(DISTINCT(id))
FROM posts
WHERE cooked ~* '\[quote'
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 <blockquote>foobar</blockquote>
[quote some_attr="some_val"]foobar[/quote] to <blockquote some_attr="some_val">foobar</blockquote>
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?
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 <br><br>foobar<br><br>
[quote some_attr="some_val"]foobar[/quote] to <br><br>foobar<br><br>