MySQL 如何从电子邮件地址中选择域名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2628138/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select domain name from email address
提问by Ugesh Gali
I have email addresses like [email protected], [email protected] [email protected]... etc.
I want a Mysql SELECTthat will trim user names and .com and return output as
gmail,ymail,hotmail, etc.
我有一个电子邮件地址[email protected]，[email protected] [email protected]...等等。我想一个MySQL SELECT，将修剪的用户名和.com和返回输出
gmail，ymail，hotmail，等。
回答by anonymous
Assuming that the domain is a single word domain like gmail.com, yahoo.com, use
假设域是单字域，如 gmail.com、yahoo.com，请使用
select (SUBSTRING_INDEX(SUBSTR(email, INSTR(email, '@') + 1),'.',1))
The inner SUBSTRgets the right part of the email address after @and the outer SUBSTRING_INDEXwill cut off the result at the first period.
内部SUBSTR会在之后获取电子邮件地址的正确部分，@而外部SUBSTRING_INDEX会在第一期截断结果。
otherwise if domain is expected to contain multiple words like mail.yahoo.com, etc, use:
否则，如果域预期包含多个单词，例如mail.yahoo.com等，请使用：
select (SUBSTR(email, INSTR(email, '@') + 1, LENGTH(email) - (INSTR(email, '@') + 1) - LENGTH(SUBSTRING_INDEX(email,'.',-1))))
LENGTH(email) - (INSTR(email, '@') + 1) - LENGTH(SUBSTRING_INDEX(email,'.',-1))will get the length of the domain minus the TLD (.com, .biz etc. part)by using SUBSTRING_INDEXwith a negative count which will calculate from right to left.
LENGTH(email) - (INSTR(email, '@') + 1) - LENGTH(SUBSTRING_INDEX(email,'.',-1))将(.com, .biz etc. part)通过使用SUBSTRING_INDEX从右到左计算的负计数来获得域的长度减去 TLD 。
回答by Dan King
I prefer:
我更喜欢：
select right(email_address, length(email_address)-INSTR(email_address, '@')) ...
so you don't have to guess how many sub-domains your user's email domain has.
所以您不必猜测您用户的电子邮件域有多少个子域。
回答by rattray
For PostgreSQL:
对于 PostgreSQL：
split_part(email, '@', 2) AS domain
Full query:
完整查询：
SELECT email, split_part(email, '@', 2) AS domain
FROM users;
Ref: http://www.postgresql.org/docs/current/static/functions-string.html
参考：http: //www.postgresql.org/docs/current/static/functions-string.html
回答by knugie
Using SUBSTRING_INDEX for "splitting" at '@' and '.' does the trick. See documentation at http://dev.mysql.com/doc/refman/5.1/de/string-functions.html#idm47531853671216.
使用 SUBSTRING_INDEX 在 '@' 和 '.' 处“拆分” 诀窍。请参阅http://dev.mysql.com/doc/refman/5.1/de/string-functions.html#idm47531853671216 上的文档。
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', 1);
Example:
例子：
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX("[email protected]", '@', -1), '.', 1);
will give you "bar".
会给你“酒吧”。
Here is what happens:
* Split "[email protected]" at '@'. --> ["foo", "bar.buz"]
* Pick first element from right (index -1). --> "bar.buz"
* Split "bar.buz" at '.' --> ["bar", "buz"]
* Pick first element (index 1) --> "bar"
Result: "bar"
下面是发生的情况：
* 在“@”处拆分“[email protected]”。--> ["foo", "bar.buz"]
* 从右边选择第一个元素（索引 -1）。--> "bar.buz"
* 在 '.' 处拆分 "bar.buz" --> ["bar", "buz"]
* 选取第一个元素（索引 1） --> "bar"
结果："bar"
If you also need to get rid of subdomains, use:
如果您还需要删除子域，请使用：
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', -2), '.', 1);
Example:
例子：
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX("[email protected]", '@', -1), '.', -2), '.', 1);
will give you "bar".
会给你“酒吧”。
回答by Joe L.
Try this, removes the @ from the domain and just leaves the domain, example: domain.com
试试这个，从域中删除@，然后离开域，例如：domain.com
select SUBSTR(SUBSTR(email_field, INSTR(email_field, '@'), INSTR(email_field, '.')), 2) as domain
回答by Julien
If you want to know the most used domain names from email addresses you have (can be usefull), you can do :
如果您想从您拥有的电子邮件地址中了解最常用的域名（可能很有用），您可以执行以下操作：
select (SUBSTRING_INDEX(SUBSTR(email, INSTR(email, '@') + 1),'.',1)) as a,count(*) as c
FROM User
group by a
order by c desc;
Result :
结果 ：
回答by Sai Varun
DECLARE @Email Varchar(20)
SET @Email='[email protected]'
SELECT SUBSTRING(@Email, CHARINDEX('@',@Email)+1,LEN(@Email))
回答by user4622420
select (SUBSTRING_INDEX(SUBSTR(email, INSTR(email, '@') + 1),'.',1) from tableName)
select (SUBSTRING_INDEX(SUBSTR(email, INSTR(email, '@') + 1),'.',1) from tableName)
Some sql statements require the table name specified where the email column belongs to.
一些sql语句需要指定email列所属的表名。
回答by Mr_KeyCode
My suggestion would be (for mysql):
我的建议是（对于 mysql）：
SELECT
LOWER(email) AS email,
SUBSTRING_INDEX(email, '@', + 1) AS account,
REPLACE(SUBSTRING_INDEX(email, '@', -1), CONCAT('.',SUBSTRING_INDEX(email, '.', -1)),'') -- 2nd part of mail - tld.
AS domain,
CONCAT('.',SUBSTRING_INDEX(email, '.', -1)) AS tld
FROM
...
ORDER BY domain, email ASC;回答by Sarfraz
Try this:
尝试这个：
select SUBSTR(field_name, INSTR(field_name, '@'), INSTR(field_name, '.'))
