MyBB Community Forums

Hi,
I converted the standard align MyCode to be simply one word MyCodes...
But I have thousands of posts...
Since MyCodes are open/close types of shortcodes, is there a way to run MySQL commands to run through the table rows and replace 

[align=center]****ignore everything between till closing tag****[/align]

to

[center]****ignore everything between till closing tag****[/center]

And the same for left,right, and justify.
I want to just clean up everything to be uniform through out the forum.
Finding and replacing a single string is easy but the logic to also change the nearest matching closing tag seems beyond the scope of any MySQL commands I am aware of?? Even if I were to export the database and manually use search and replace, it won't cover the logic needed to do the proper replacing I need. Thanks for any help.

---

Update:
Please see Post #17 for the logic problem we came up against.
currently 

\[align=(center|justify|left|right)\](.*)\[/align\] 

does not account for nesting issues, validly nested tags should be what's replaced while the invalidly nested groups should be left alone so I can go in and change them manually. At least that's what it seems would solve the issue.

---

SOLVED!
This works perfectly in Sublime Text with grep enabled in Search and Replace:
Search (grep):

\[align\=(left|center|right|justify)\](.*?)\[/align\]

Replace:

[$1]$2[/$1]

You cannot do that directly in MySQL, because there's no way to use regexp for replacement.

The simpliest way:
- turn off your forum
- backup your mybb_posts table
- use sed or a good code editor to perform
search \[align=(center|justify|left|right)\](.*)\[/align\]
replace: \[$1\]$2\[/$1\]
- reinject
- turn on your forum

(2015-02-27, 12:27 PM)Crazycat Wrote: [ -> ]You cannot do that directly in MySQL, because there's no way to use regexp for replacement.

The simpliest way:
- turn off your forum
- backup your mybb_posts table
- use sed or a good code editor to perform
search \[align=(center|justify|left|right)\](.*)\[/align\]
replace: \[$1\]$2\[/$1\]
- reinject
- turn on your forum

Thank you, I'll give it a shot, will see is sublime text will do the trick

You can also just replace the tags:

UPDATE `mybb_posts` SET message = replace(message, '[align=center]', '[center]');
UPDATE `mybb_posts` SET message = replace(message, '[/align]', '[/center]');

@doylecc : I don't think he have only center alignements in his posts, he has to manage with the 4 options.

I know It was just an example.

Hey, this Regexp is not working for some reason, it's catching the first

[align=center]

---

(2015-02-27, 12:54 PM)doylecc Wrote: [ -> ]You can also just replace the tags:

UPDATE `mybb_posts` SET message = replace(message, '[align=center]', '[center]');
UPDATE `mybb_posts` SET message = replace(message, '[/align]', '[/center]');

Thank you, yea but if I do this it will put close center on the wrong sections...

(2015-02-27, 01:27 PM)WebDevandPhoto Wrote: [ -> ]

it finds but then it skips past a ton of other embedded aligns covering a 1/8 to 1/6 chuck of the 10,000+ post table to a closing tag skipping a ton of opening and closing tags... . SO the search string might need some fixing? I'm terrible with regex. I'm using Sublime Text expression search and replace function.

It's a trouble with the (un)greedy flag in the search.

I'll try to install Sublime text and find the way to correct that.

EDIT
I don't think Sublime Text allows the required options to perform this search, or it might be in the config files

Thank you Crazycat, what should I use on OSX to do this besides Sublime.. if sublime is a no go?

(2015-02-27, 01:27 PM)WebDevandPhoto Wrote: [ -> ]Thank you, yea but if I do this it will put close center on the wrong sections...

Yes, sorry. I forgot to consider the align closing tag is used for left, right and justify too.

Crazycat's suggestion of using a regex pattern is the better way here.
This should work in most editors though I only tested it with sublime text and geany:

search \[(align=(center|justify|left|right))\](.*)\[/(align)\]
replace: [\2]\3[/\2]