MySQL:在某些字符之间查找和替换

Question

在字段 post_content 中，我有将近 800 行的字符串:

http://somesite.com/">This is some site</a>

我需要删除从 "> 开始的所有内容，以便它只留下 URL。我无法直接查找和替换，因为文本是唯一的。

有什么线索吗？这确实是我第一次涉足 MySQL 数据库修改，但在发帖之前我确实进行了广泛的搜索。

谢谢，

~凯尔~

Answer 1

来自本网站:http://www.regular-expressions.info/mysql.html

LIB_MYSQLUDF_PREG

If you want more regular expression power in your database, you can consider using LIB_MYSQLUDF_PREG. This is an open source library of MySQL user functions that imports the PCRE library. LIB_MYSQLUDF_PREG is delivered in source code form only. To use it, you'll need to be able to compile it and install it into your MySQL server. Installing this library does not change MySQL's built-in regex support in any way. It merely makes the following additional functions available:

来了...

PREG_CAPTURE extracts a regex match from a string. PREG_POSITION returns the position at which a regular expression matches a string. PREG_REPLACE performs a search-and-replace on a string. PREG_RLIKE tests whether a regex matches a string.

听起来正是您要找的。

All these functions take a regular expression as their first parameter. This regular expression must be formatted like a Perl regular expression operator. E.g. to test if regex matches the subject case insensitively, you'd use the MySQL code PREG_RLIKE('/regex/i', subject). This is similar to PHP's preg functions, which also require the extra // delimiters for regular expressions inside the PHP string.