CODEWITHSUNDEEP
mysqlregex
Igal
Igal

Reputation: 13

Replace substring using REGEXP in MYSQL

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.

Upvotes: 1

Views: 3390

Answers (1)

planestepper
planestepper

Reputation: 3317

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.

Upvotes: 2

Related Questions