sql help needed

[Meatshield]

I currently have loads of posts wrapped it code tags
ie:

[code]post[/code]

I have just installed a hide mod and want to have all the older posts wrapped in code tags hidden aswell
so there like this:

[hide][ code]post[/ code][/hide]

Is there a way to insert the hide code via an sql quiry rather than having to edit everypost 1 by 1?

[Farcaster]

If you are going to want the code tag to always be encapsulated by the hide tag, perhaps you might modify the code tag? But, yes, you could use the mySQL replace function to update the posts.

Here's the code you would probably use (untested, so use at your own risk and backup your data):

[sql]UPDATE post
SET pagetext = REPLACE(REPLACE(REPLACE(REPLACE(pagetext,'

','[HIDE]

	
	
'),'
','

[/HIDE]'),'

','[hide]

	
	
'),'
','

[/hide]')
WHERE pagetext LIKE '%

%' AND pagetext LIKE '%

%';[/sql]

Note that the mySQL replace function is case sensitive, so this will replace anything that is either tagged in all uppercase or all lower case. It will not find tags with mixed case...

Because vBulletin stores a cache of parsed posts, you'll most likely need to clear this cache before you see the posts updated. You can do that by executing this SQL:

[SQL]TRUNCATE TABLE postparsed;[/SQL]

Following that you can rebuild the post cache if you wish or let it rebuild on its own. If you want to rebuild it, in the AdminCP goto Maintenance -> Update Counters -> Rebuild Post Cache


EDIT: That's annoying... Note that vBulletin automatically lowered the capitilzation in that SQL query to "prevent shouting." So, when you execute it, be sure to make the first 4 instances of the word "code" uppercase.

[Meatshield]

It worked great many thanks

[Farcaster]

No trouble at all.