mysql - 通过MySQL数据库中的正则表达式选择列的一部分

我有一个带有电子邮件地址的表，它们都像reddit。IDENTIFIER @ redirthis.url
如何运行仅选择identifier的查询？
表emails

+----+--------------------------------+
| id | email                          |
+----+--------------------------------+
|  1 | [email protected]     |
|  2 | [email protected]     |
|  3 | [email protected] |
+----+--------------------------------+

预期/期望结果

+----+------------+
| id | email      |
+----+------------+
|  1 | A73283     |
|  2 | XAAX83     |
|  3 | A73283F3GH |
+----+------------+

最佳答案

如果模式相同，则可以使用substring_index

mysql> select substring_index(substring_index('[email protected]','@',1),'.',-1);
+-----------------------------------------------------------------------------+
| substring_index(substring_index('[email protected]','@',1),'.',-1) |
+-----------------------------------------------------------------------------+
| A73283                                                                      |
+-----------------------------------------------------------------------------+

所以查询将是

select
id,
substring_index(substring_index(email,'@',1),'.',-1) as email
from emails