在 MYSQL 中使用 REGEXP 替换子字符串 [英] Replace substring using REGEXP in MYSQL

问题描述

在我的论坛中，我有一个 BB 代码来显示 youtube 视频.以下选项可用于提取 youtube 视频代码并使用此代码构建嵌入代码:

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]

所有这些选项都返回 YouTube 视频代码 __OBlgSz8sSM

All these options return the youtube video code __OBlgSz8sSM

升级到新版论坛后，前两个选项解析 youtube 链接不起作用，只有最后一个选项 [youtube]_OBlgSz8sSM[/youtube] 起作用.

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.

这些 BB 代码是论坛帖子的一部分，而不是在数据库中的单独字段中.

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

我的目标是用

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

我正在使用 MySQL表名是 POST包含这些 BB 代码的字段名称是 PAGETEXT

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.

推荐答案

以前的答案完全错误.

Former answer was plain wrong.

不使用正则表达式:

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?

使用测试数据:

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.

总是先测试.

这篇关于在 MYSQL 中使用 REGEXP 替换子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！