通过REGEX在MySQL中解析结果 [英] parse results in MySQL via REGEX
问题描述
我对REGEX支持MySQL的功能有些困惑，我还没有找到一个可靠的示例来说明如何使用REGEX within an sql statement分隔结果.
I'm a bit confused on the functionality of the REGEX support for MySQL and I have yet to find a solid example on how to separate a result with REGEX within an sql statement.
示例:
我该如何从表格电子邮件中提取看起来像...的数据
How could I pull data from a table emails that looks something like...
+-------------------------+
|Emails |
|-------------------------|
|Some.email@yourDomain.com|
+-------------------------+
并通过看起来像...的sql语句返回某些内容.
and return something through an sql statement that looks like...
+------------------------------+
|Username | Domain | TLD |
|-----------|------------|-----|
|some.email | yourdomain | com |
+------------------------------+
推荐答案
MySQL没有内置功能来满足您的要求.可以通过定义一些新功能来实现，但是使用您通过其访问数据库的任何编程语言来进行操作都可能会更容易.
MySQL doesn't have built-in functionality to do what you're asking for. It would be possible by defining some new functions, but it's probably easier to just do it in whatever programming language you're accessing the database through.
对于像电子邮件地址这样简单的内容，您根本不需要使用正则表达式，可以使用
For something as simple as an email address though, you shouldn't need to use regular expressions at all, you can use the SUBSTRING_INDEX() function, as:
SELECT
SUBSTRING_INDEX(email, '@', 1) AS Username,
SUBSTRING_INDEX(SUBSTR(email, LOCATE('@', email)), '.', 1) AS Domain,
SUBSTRING_INDEX(email, '.', -1) AS TLD
FROM users;
换句话说，就是:
- 用户名=第一个'@'之前的所有内容
- 域=第一个"@"和第一个."之间的所有内容.
- TLD =最后一个'.'之后的所有内容.
这篇关于通过REGEX在MySQL中解析结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
