Query for word replacement like in a text editor?

[indie]

I have a forum, and in the db I want to update some bbcode.

 

With the new forum software, when a youtube URL is pasted it embeds. I want to remove the old bbcode, but that will render previous posts unembedded. I have a solution, I just don't know how to do it. I need to do a replace all, similar to that in a text editor.

 

The old bbcode is:

[yt]video_number_here[/yt]

 

In a text editor if I search for [yt] and replace all with http://www.youtube.com/watch?v=

I get:

http://www.youtube.com/watch?v=video_number_here[/yt]

 

Now to remove the end tag, I search for [/yt] and replace all with the replacement field blank, and I get:

http://www.youtube.com/watch?v=video_number_here

 

Well that's how it would be done in a text editor, but I have no idea how I would do this in my db. Are there any queries that could do this? And please let me know if they are case sensitive, for the [yt].

 

Thanks for any help.

 

 

[Pikachu2000]

MySQL REPLACE() function. As far as case-sensitivity, I'm not sure, so you'll have to run some tests.

 

UPDATE `table` SET `field` = REPLACE(`field`, '[yt]', 'http://www.youtube.com/watch?v='), `field` = REPLACE(`field`, '[/yt]', '')

[indie]

Awesome, so this is possible!!

 

I could also replace with tags, I would just need to put that youtube URL after the first opening tag, and then replace the closing tag.

 

So if I did that instead, the code would be:

 

UPDATE `table` SET `field` = REPLACE(`field`, '[yt]', '[media]http://www.youtube.com/watch?v='), `field` = REPLACE(`field`, '[/yt]', '[/media]')

 

And this:

[yt]video_number_here[/yt]

 

Becomes:

[media]http://www.youtube.com/watch?v=video_number_here

 

Correct? No spacing, or line break issues?

 

Thanks so much. I'll have to test on a test db before trying live.

[Pikachu2000]

Yes, that looks right. I dunno about line breaks/spacing issues for certain, though I've never run into any. But as you say, testing it on a dev DB first would definitely be a good idea.

[indie]

Awesome, thanks.