Replace substring using REGEXP in MYSQL

Question

In my forum I have a BB code to display youtube videos. following options can be used to extract youtube video code and use this code to build the embed code:

[youtube]http://www.youtube.com/watch?v=_OBlgSz8sSM&feature=related[/youtube]
[youtube]http://www.youtube.com/watch?v=_OBlgSz8sSM[/youtube]
[youtube]_OBlgSz8sSM[/youtube]

All these options return the youtube video code __OBlgSz8sSM

After upgrade to new version of forum the first two options with parsing of youtube link not working and only the last option with [youtube]_OBlgSz8sSM[/youtube] is work.

These BB codes are part of forum posts and not in separate field in database.

My goal is to replace all sub strings that start with [youtube] AND end with [/youtube] that contain ?v= with

[youtube]11 characters from ?v=[/youtube]

I am using MySQL table name is POST field name that contain these BB codes is PAGETEXT

Can you please assist me to build this update.

I don't think mySQL is equipped to do this - as far as I'm aware, its regular expressions are for selecting only, not replacing. Can't you use PHP or something to do the actual replacement? — Pekka
– Pekka, Commented Jun 18, 2011 at 16:19
In previous version of forum it was a module that did this parsing. This module is not available in new version and I am not programmer to write this module. — Igal
– Igal, Commented Jun 18, 2011 at 16:23

planestepper · Accepted Answer · 2011-06-23 12:51:40Z

2

EDIT: Former answer was plain wrong.

Without using regex:

UPDATE POST SET PAGETEXT = REPLACE(PAGETEXT, '[youtube]http://www.youtube.com/watch?v=', '[youtube]');
UPDATE POST SET PAGETEXT = REPLACE(PAGETEXT, '[youtube]www.youtube.com/watch?v=', '[youtube]');
UPDATE POST SET PAGETEXT = REPLACE(PAGETEXT, '[youtube]youtube.com/watch?v=', '[youtube]');
UPDATE POST SET PAGETEXT = REPLACE(PAGETEXT, '&feature=related[/youtube]', '[/youtube]');

This will remove the data you don't want through a direct search and replace. Are there any other patterns/formats that are not listed on the original post?

Using the test data:

SET @test = 'test [youtube]youtube.com/watch?v=_OBlgSz8sSM&feature=related[/youtube] test';
SET @test = REPLACE(@test, '[youtube]http://www.youtube.com/watch?v=', '[youtube]');
SET @test = REPLACE(@test, '[youtube]www.youtube.com/watch?v=', '[youtube]');
SET @test = REPLACE(@test, '[youtube]youtube.com/watch?v=', '[youtube]');
SET @test = REPLACE(@test, '&feature=related[/youtube]', '[/youtube]');
SELECT @test;

mysql> SELECT @test;
+------------------------------------------+
| @test                                    |
+------------------------------------------+
| test [youtube]_OBlgSz8sSM[/youtube] test | 
+------------------------------------------+

After running these four, it's interesting to run a

SELECT COUNT(*) FROM POST WHERE PAGETEXT LIKE '%?v=%';

and then ensure if there's anything else that needs handling.

Always test first.

edited Jun 23, 2011 at 12:51

answered Jun 23, 2011 at 0:49

planestepper

3,32729 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Igal Over a year ago

@leon, thanks for the reply. I am afraid after applying that the [youtube]youtube.com/watch?v=_OBlgSz8sSM[/youtube] will be changed to [youtube]youtube.com/watch[youtube]_OBlgSz8sSM[/youtube]=_OBlgSz8sSM[/… because you changing only the ?v= and not all the string that start from [youtube] and ends with [/youtube]

planestepper Over a year ago

@Igal, no, it won't. The update works like this: (1) select all characters that are AFTER the delimiter (in this case, ?v=) so it wields _OBlgSz8sSM&feature=related[/youtube] then (2) get the FIRST 11 characters, wielding _OBlgSz8sSM then (3) prepend [youtube] and append [/youtube] to the string wielded in steps 1 and 2. ;-)

Igal Over a year ago

@leon, I have tested your suggestion. It replaced all the text in the column, not only from [youtube] to [/youtube]

Igal Over a year ago

"test [youtube]youtube.com/watch?v=_OBlgSz8sSM&feature=related[/youtube] test" replaced with "[youtube]_OBlgSz8sSM[/youtube]"

planestepper Over a year ago

@Igal, my bad: you had already mentioned These BB codes are part of forum posts and not in separate field in database. I hope you did have a backup of it :-( I then assume there'll be N [youtube]_url_[/youtube] within each PAGETEXT field. I'll be updating by answer in a few minutes to support this.

|

Collectives™ on Stack Overflow

Replace substring using REGEXP in MYSQL

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related