blocks|key|333990|text|假设该域是像gmail.com、yahoo.com这样的单个单词域，使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|333991|select+(SUBSTRING_INDEX(SUBSTR(email,+INSTR(email,+'@')+%2B+1),'.',1))|code-block|syntax|javascript|333992|内部SUBSTR在@之后获取电子邮件地址的正确部分，外部SUBSTRING_INDEX将在第一个周期截断结果。|offset|length|style|CODE|333993|否则，如果域需要包含多个单词，如mail.yahoo.com等，请使用：|333994|select+(SUBSTR(email,+INSTR(email,+'@')+%2B+1,+LENGTH(email)+-+(INSTR(email,+'@')+%2B+1)+-+LENGTH(SUBSTRING_INDEX(email,'.',-1))))+|333995|LENGTH(email)+-+(INSTR(email,+'@')+%2B+1)+-+LENGTH(SUBSTRING_INDEX(email,'.',-1))将通过使用带有负数的SUBSTRING_INDEX获得域的长度减去TLD+(.com,+.biz+etc.+part)，这将从右到左计算。|333996|entityMap^0|0|0|2|6|9|1|S|F|0|G|E|0|0|0|27|2H|F|38|M|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]|$I|Z|J|10|K|L]|$I|11|J|12|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|13|8|@$I|14|J|15|K|L]]|9|@]|A|$]]|$1|O|3|P|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|17|8|@$I|18|J|19|K|L]|$I|1A|J|1B|K|L]|$I|1C|J|1D|K|L]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|T|$]]

Assuming that the domain is a single word domain like gmail.com, yahoo.com, use

<pre><code>select (SUBSTRING_INDEX(SUBSTR(email, INSTR(email, '@') + 1),'.',1))
</code></pre>

The inner <code>SUBSTR</code> gets the right part of the email address after <code>@</code> and the outer <code>SUBSTRING_INDEX</code> will cut off the result at the first period.

otherwise if domain is expected to contain multiple words like <code>mail.yahoo.com</code>, etc, use: 

<pre><code>select (SUBSTR(email, INSTR(email, '@') + 1, LENGTH(email) - (INSTR(email, '@') + 1) - LENGTH(SUBSTRING_INDEX(email,'.',-1)))) 
</code></pre>

<code>LENGTH(email) - (INSTR(email, '@') + 1) - LENGTH(SUBSTRING_INDEX(email,'.',-1))</code> will get the length of the domain minus the TLD <code>(.com, .biz etc. part)</code> by using <code>SUBSTRING_INDEX</code> with a negative count which will calculate from right to left.

blocks|key|1082862|text|我更喜欢：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1082863|select+right(email_address,+length(email_address)-INSTR(email_address,+'@'))+...|code-block|syntax|javascript|1082864|因此，您不必猜测您的用户的电子邮件域有多少子域。|1082865|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I prefer:

<pre><code>select right(email_address, length(email_address)-INSTR(email_address, '@')) ...
</code></pre>

so you don't have to guess how many sub-domains your user's email domain has.

blocks|key|334942|text|对于PostgreSQL：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|334943|split_part(email,+'@',+2)+AS+domain|code-block|syntax|javascript|334944|完整查询：|334945|SELECT+email,+split_part(email,+'@',+2)+AS+domain
FROM+users;|334946|参考：http://www.postgresql.org/docs/current/static/functions-string.html|offset|length|334947|归功于https://stackoverflow.com/a/19230892/1048433|334948|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/static/functions-string.html|1|https://stackoverflow.com/a/19230892/1048433^0|0|0|0|0|3|1V|0|0|3|18|1|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|14|8|@]|9|@$M|15|N|16|1|17]]|A|$]]|$1|O|3|P|5|6|7|18|8|@]|9|@$M|19|N|1A|1|1B]]|A|$]]|$1|Q|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]|Y|$5|T|U|V|A|$W|Z]]]]

For PostgreSQL: 

<pre><code>split_part(email, '@', 2) AS domain
</code></pre>

Full query: 

<pre><code>SELECT email, split_part(email, '@', 2) AS domain
FROM users;
</code></pre>

Ref: <a href="http://www.postgresql.org/docs/current/static/functions-string.html" rel="noreferrer">http://www.postgresql.org/docs/current/static/functions-string.html</a>

Credit to <a href="https://stackoverflow.com/a/19230892/1048433">https://stackoverflow.com/a/19230892/1048433</a>

blocks|key|1630401|text|在“@”和“‘”处使用SUBSTRING_INDEX进行“拆分”真的很管用。请参阅http://dev.mysql.com/doc/refman/5.1/de/string-functions.html#idm47531853671216上的文档。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1630402|SELECT+SUBSTRING_INDEX(SUBSTRING_INDEX(email,+'@',+-1),+'.',+1);|code-block|syntax|javascript|1630403|示例：|1630404|SELECT+SUBSTRING_INDEX(SUBSTRING_INDEX("foo@bar.buz",+'@',+-1),+'.',+1);|1630405|会给你一个“酒吧”。|1630406|下面是发生的情况：|1630407|*在'@‘处拆分"foo@bar.buz“。-->+"foo"，"bar.buz“|1630408|*从右侧选取第一个元素(index+-1)。-->+"bar.buz“|1630409|*在'.‘处拆分"bar.buz“-->+"bar"，"buz”|1630410|*选择第一个元素(索引1)+-->+"bar“|1630411|结果："bar“|1630412|如果您还需要去掉子域，请使用：|1630413|SELECT+SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(email,+'@',+-1),+'.',+-2),+'.',+1);|1630414|1630415|SELECT+SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX("foo@1.2.3.bar.buz",+'@',+-1),+'.',+-2),+'.',+1);|1630416|1630417|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/de/string-functions.html#idm47531853671216^0|15|26|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1G|8|@]|9|@$A|1H|B|1I|1|1J]]|C|$]]|$1|D|3|E|5|F|7|1K|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|1L|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|1M|8|@]|9|@]|C|$G|H]]|$1|M|3|N|5|6|7|1N|8|@]|9|@]|C|$]]|$1|O|3|P|5|6|7|1O|8|@]|9|@]|C|$]]|$1|Q|3|R|5|6|7|1P|8|@]|9|@]|C|$]]|$1|S|3|T|5|6|7|1Q|8|@]|9|@]|C|$]]|$1|U|3|V|5|6|7|1R|8|@]|9|@]|C|$]]|$1|W|3|X|5|6|7|1S|8|@]|9|@]|C|$]]|$1|Y|3|Z|5|6|7|1T|8|@]|9|@]|C|$]]|$1|10|3|11|5|6|7|1U|8|@]|9|@]|C|$]]|$1|12|3|13|5|F|7|1V|8|@]|9|@]|C|$G|H]]|$1|14|3|J|5|6|7|1W|8|@]|9|@]|C|$]]|$1|15|3|16|5|F|7|1X|8|@]|9|@]|C|$G|H]]|$1|17|3|N|5|6|7|1Y|8|@]|9|@]|C|$]]|$1|18|3|-4|5|6|7|1Z|8|@]|9|@]|C|$]]]|19|$1A|$5|1B|1C|1D|C|$1E|1F]]]]

Using SUBSTRING_INDEX for "splitting" at '@' and '.' does the trick. See documentation at <a href="http://dev.mysql.com/doc/refman/5.1/de/string-functions.html#idm47531853671216" rel="noreferrer">http://dev.mysql.com/doc/refman/5.1/de/string-functions.html#idm47531853671216</a>.

<pre><code>SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', 1);
</code></pre>

<h3>Example:</h3>

<pre><code>SELECT SUBSTRING_INDEX(SUBSTRING_INDEX("foo@bar.buz", '@', -1), '.', 1);
</code></pre>

will give you "bar".

Here is what happens: 
 * Split "foo@bar.buz" 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"

If you also need to get rid of subdomains, use:

<pre><code>SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', -2), '.', 1);
</code></pre>

<h3>Example:</h3>

<pre><code>SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX("foo@1.2.3.bar.buz", '@', -1), '.', -2), '.', 1);
</code></pre>

will give you "bar".

blocks|key|3039071|text|如果你想从你拥有的电子邮件地址中知道最常用的域名(可能很有用)，你可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3039072|select+(SUBSTRING_INDEX(SUBSTR(email,+INSTR(email,+'@')+%2B+1),'.',1))+as+a,count(*)+as+c
FROM+User
group+by+a
order+by+c+desc;|code-block|syntax|javascript|3039073|结果：|3039074|​|3039075|📷|atomic|offset|length|3039076|3039077|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/62d7b10c125ecedc4fb89b078d1f340c.jpeg|imageAlt^0|0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|11|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|12|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|13|8|@]|9|@$N|14|O|15|1|16]]|A|$]]|$1|P|3|J|5|6|7|17|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X|Y|-4]]]]

If you want to know the most used domain names from email addresses you have (can be usefull), you can do :

<pre><code>select (SUBSTRING_INDEX(SUBSTR(email, INSTR(email, '@') + 1),'.',1)) as a,count(*) as c
FROM User
group by a
order by c desc;
</code></pre>

Result :

<a href="https://i.stack.imgur.com/MGhLH.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/MGhLH.jpg" alt="enter image description here"></a>

blocks|key|1630618|text|DECLARE+@Email+Varchar(20)
SET+@Email='abc@gmail.com'
SELECT+SUBSTRING(@Email,+CHARINDEX('@',@Email)%2B1,LEN(@Email))|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1630619|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>DECLARE @Email Varchar(20)
SET @Email='abc@gmail.com'
SELECT SUBSTRING(@Email, CHARINDEX('@',@Email)+1,LEN(@Email))
</code></pre>

blocks|key|3038831|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3038832|select+SUBSTR(field_name,+INSTR(field_name,+'@'),+INSTR(field_name,+'.'))|code-block|syntax|javascript|3038833|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>select SUBSTR(field_name, INSTR(field_name, '@'), INSTR(field_name, '.'))
</code></pre>

blocks|key|334046|text|select+(SUBSTRING_INDEX(SUBSTR(email，INSTR(email，'@')+%2B+1)，‘.，1)+from+tableName)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|334047|某些sql语句要求指定电子邮件列所属的表名。|334048|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|3038972|text|我的建议是(对于mysql)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3038973|​|3038974|SELECT+
++++LOWER(email)+AS+email,
++++SUBSTRING_INDEX(email,+'@',+%2B+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;|code-block|syntax|javascript|3038975|3038976|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|N|8|@]|9|@]|A|$G|H]]|$1|I|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|P|8|@]|9|@]|A|$]]]|K|$]]

My suggestion would be (for mysql):

<div class="snippet" data-lang="js" data-hide="false">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override"><code>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;</code></pre>
</div>
</div>

blocks|key|334116|text|DECLARE+@Email+Varchar(20)
SET+@Email='abc@gmail.com'
SELECT+RIGHT(@Email,LEN(@Email)-CHARINDEX('@',@Email))|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|334117|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>DECLARE @Email Varchar(20)
SET @Email='abc@gmail.com'
SELECT RIGHT(@Email,LEN(@Email)-CHARINDEX('@',@Email))
</code></pre>

blocks|key|334144|text|select+SUBSTR(email,+instr(email,+'@')+%2B1,+instr(email,'.')+-3)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|334145|使用下面的查询可以得到确切的答案|334146|entityMap^0|0|1R|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

<code>select SUBSTR(email, instr(email, '@') +1, instr(email,'.') -3)</code>
exact answer is coming using this query

blocks|key|1630733|text|对于MSSQL|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1630734|declare+@test+as+varchar(15)+=+'foo@bar.buz'
SELECT+SUBSTRING(@test,+CHARINDEX('@',+@test)+%2B+1,+LEN(@test))+AS+ExtractString;|code-block|syntax|javascript|1630735|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For MSSQL
<pre><code>declare @test as varchar(15) = 'foo@bar.buz'
SELECT SUBSTRING(@test, CHARINDEX('@', @test) + 1, LEN(@test)) AS ExtractString;
</code></pre>

blocks|key|3039119|text|SELECT+SUBSTR(NAME,INSTR(NAME,'@')%2B1)+FROM+ORACLE;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3039120|甲骨文是我的表，别搞混了。|unstyled|3039121|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT SUBSTR(NAME,INSTR(NAME,'@')+1) FROM ORACLE;
</code></pre>

Oracle is my table.Don't be confuse.

I have email addresses like <code>user1@gmail.com</code>, <code>user2@ymail.com user3@hotmail.com</code> ... etc.
I want a Mysql <code>SELECT</code> that will trim user names and .com and return output as
<code>gmail</code>,<code>ymail</code>,<code>hotmail</code>, etc.

How to select domain name from email address

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有像user1@gmail.com，user2@ymail.com user3@hotmail.com这样的电子邮件地址。等。我想要一个Mysql的SELECT，将削减用户名和.com，并返回输出为gmail，ymail，hotmail等。

问如何从电子邮件地址中选择域名
EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从电子邮件地址中选择域名EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从电子邮件地址中选择域名
EN