How to select domain name from email address

Question

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

score 86 · Accepted Answer · edited Mar 24 '16 at 08:47

86

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

select (SUBSTRING_INDEX(SUBSTR(email, INSTR(email, '@') + 1),'.',1))

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

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

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_INDEX with a negative count which will calculate from right to left.

edited Mar 24 '16 at 08:47

Bud Damyanov

30,171
6
44
52

answered Apr 13 '10 at 09:18

anonymous

3,474
25
29

1

Hi ugesh gali, could you then click on the checkmark next to this answer to mark the question as answered? Thanks! – anonymous Apr 13 '10 at 09:54
1

the term you're looking for is top-level-domain. and here's wikipedia for you: http://en.wikipedia.org/wiki/Top-level_domain – Devin May 08 '12 at 20:03
38

this part will return all characters after the '@': ``SUBSTR(email, INSTR(email, '@') + 1) `` – s2t2 Nov 21 '13 at 20:14

score 74 · Answer 2 · edited Nov 11 '13 at 05:40

74

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.

edited Nov 11 '13 at 05:40

Noel

10,152
30
45
67

answered Nov 11 '13 at 05:31

Dan King

841
6
2

If you come to this question and you're using PostgreSQL, use `STRPOS` instead of `INSTR`. – Ostap Andrusiv Jul 31 '19 at 09:43
I had tried the answers above but some of my domains have 4 parts. Your answer fixed the problem! Thanks! +1 – KingAndrew Sep 01 '20 at 15:31
1

At least with MariaDB this shorter version works too: select substr(email, instr(email, '@')+1) – TheAmigo Jan 25 '22 at 19:55

score 22 · Answer 3 · edited May 23 '17 at 12:26

22

For 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

Credit to https://stackoverflow.com/a/19230892/1048433

edited May 23 '17 at 12:26

Community

1
1

answered Apr 21 '16 at 21:54

rattray

5,174
1
33
27

Brillant answer! – rtrojanowski Nov 24 '20 at 14:57
1

The question is tagged `mysql` – Kermit Apr 26 '21 at 18:12

wteuber · Answer 4 · 2014-04-30T14:41:36.680

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.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', 1);

Example:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX("foo@bar.buz", '@', -1), '.', 1);

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:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', -2), '.', 1);

Example:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX("foo@1.2.3.bar.buz", '@', -1), '.', -2), '.', 1);

will give you "bar".

score 7 · Answer 5 · answered May 03 '16 at 08:27

7

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 :

answered May 03 '16 at 08:27

Julien

3,743
9
38
67

score 5 · Answer 6 · answered May 10 '13 at 05:06

5

Try this, removes the @ from the domain and just leaves the domain, example: domain.com

select SUBSTR(SUBSTR(email_field, INSTR(email_field, '@'), INSTR(email_field, '.')), 2) as domain

answered May 10 '13 at 05:06

Joe L.

4,433
1
19
14

Sai Varun · Answer 7 · 2017-07-21T12:28:32.807

3

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

edited Jul 21 '17 at 12:28

answered Jan 23 '17 at 09:49

Sai Varun

136
1
6

score 2 · Answer 8 · answered Apr 13 '10 at 08:51

2

Try this:

select SUBSTR(field_name, INSTR(field_name, '@'), INSTR(field_name, '.'))

answered Apr 13 '10 at 08:51

Sarfraz

377,238
77
533
578

score 2 · Answer 9 · answered Mar 02 '15 at 07:36

2

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.

answered Mar 02 '15 at 07:36

user4622420

21
1

score 1 · Answer 10 · answered Apr 14 '15 at 13:53

My suggestion would be (for 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;

score 1 · Answer 11 · edited Jul 21 '17 at 12:34

1

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

edited Jul 21 '17 at 12:34

Frits

7,341
10
42
60

answered Jul 21 '17 at 12:32

Sai Varun

136
1
6

1

Please remember to use the code formatting functionality of the editor when posting code. Also, on a side note, is there a reason you didn't just edit your previous answer to add this to it as a secondary solution? And finally, please remember that a __good answer__ is often accompanied by an explanation of the code (why/how it was used). Reading and following through [How to answer](https://stackoverflow.com/help/how-to-answer) will help to drastically increase the quality of your answers. :) – Frits Jul 21 '17 at 12:37
Thanks @Frits. I'ma beginner. Thanks for your suggestions. How do i add a secondary solution to the same answer? How do i use code formatting functionality? – Sai Varun Jul 21 '17 at 13:00
Not a problem. At the bottom-left of your answer (before the comments) there is an _edit_ button, simply edit your answer to include your updated code. A line of code in your answer should always be preceded by 4 spaces - have a look at [Markdown Editing Help](https://stackoverflow.com/editing-help) :) – Frits Jul 21 '17 at 13:03

score 1 · Answer 12 · edited Jun 20 '23 at 11:23

1

MYSQL:

SELECT mail,
        RIGHT(mail,LENGTH(mail)-LOCATE('@',mail)) as domain_name
        FROM email;

edited Jun 20 '23 at 11:23

Ankit Jindal

3,672
3
25
37

answered Jun 13 '23 at 11:21

Lekhnath

11
1

Thanks ! This one works fine for emails like john.doe@test.com – William Desportes Jun 20 '23 at 15:13

score 0 · Answer 13 · edited Feb 02 '21 at 11:17

0

select SUBSTR(email, instr(email, '@') +1, instr(email,'.') -3)

exact answer is coming using this query

edited Feb 02 '21 at 11:17

Jayantha Lal Sirisena

21,216
11
71
92

answered Mar 02 '20 at 14:04

rajeswari nollu

1
1

1

Welcome to SO. Your answer does not appear to provide narrative or substantive difference from the accepted answer. Please, when providing an answer, explain why the code will help the OP (or someone with a similar question) and format your code. – jessi Mar 02 '20 at 15:45

score 0 · Answer 14 · answered Oct 08 '21 at 17:14

0

For MSSQL

declare @test as varchar(15) = 'foo@bar.buz'
SELECT SUBSTRING(@test, CHARINDEX('@', @test) + 1, LEN(@test)) AS ExtractString;

answered Oct 08 '21 at 17:14

Melu

1,835
3
14
13

score -1 · Answer 15 · edited May 21 '18 at 05:04

-1

SELECT SUBSTR(NAME,INSTR(NAME,'@')+1) FROM ORACLE;

Oracle is my table.Don't be confuse.

edited May 21 '18 at 05:04

sticky bit

36,626
12
31
42

answered May 21 '18 at 03:39

Mayank Agrawal

1

How to select domain name from email address

15 Answers15

Example:

Example:

Linked

Related