CODEWITHSUNDEEP
mysql
Ugesh Gali
Ugesh Gali

Reputation: 2152

How to select domain name from email address

I have email addresses like [email protected], [email protected] [email protected] ... etc. I want a Mysql SELECT that will trim user names and .com and return output as gmail,ymail,hotmail, etc.

Upvotes: 69

Views: 167592

Answers (17)

ino
ino

Reputation: 2581

Both MySQL and MariaDB has function SUBSTRING_INDEX(str,delim,count) that splits the string str by delimiter delim and returns before/after part of the string based on sign of count where positive returns before, negative after the delimiter.

An example with multilevel domain name [email protected] email address:

SET @email='[email protected]';

SELECT 
  SUBSTRING_INDEX(@email, '@', 1) as 'user_name', -- All before @ char, ie. user.name
  SUBSTRING_INDEX(@email, '@', -1) as 'full_domain', -- All after @ char, ie. example.dot.com
  SUBSTRING_INDEX(@email, '.', -1) as 'top_level_domain' -- All after last dot, ie. com
;

Results:

user_name full_domain top_level_domain
user.name example.dot.com com

See the MariaDB documentation of the SUBSTRING_INDEX(str,delim,count)

Upvotes: 0

Pritam Pallab
Pritam Pallab

Reputation: 137

Simple select distinct(SUBSTR(email, INSTR(email, '@') + 1, LENGTH(email))) from MY_TABLE;

Upvotes: 0

Lekhnath
Lekhnath

Reputation: 11

MYSQL:

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

Upvotes: 1

Melu
Melu

Reputation: 1905

For MSSQL

declare @test as varchar(15) = '[email protected]'
SELECT SUBSTRING(@test, CHARINDEX('@', @test) + 1, LEN(@test)) AS ExtractString;

Upvotes: 0

rajeswari nollu
rajeswari nollu

Reputation: 1

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

exact answer is coming using this query

Upvotes: 0

Mayank Agrawal
Mayank Agrawal

Reputation: 1

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

Oracle is my table.Don't be confuse.

Upvotes: -1

Sai Varun
Sai Varun

Reputation: 156

DECLARE @Email Varchar(20)
SET @Email='[email protected]'
SELECT RIGHT(@Email,LEN(@Email)-CHARINDEX('@',@Email))

Upvotes: 1

Sai Varun
Sai Varun

Reputation: 156

DECLARE @Email Varchar(20)
SET @Email='[email protected]'
SELECT SUBSTRING(@Email, CHARINDEX('@',@Email)+1,LEN(@Email))

Upvotes: 3

Julien
Julien

Reputation: 4163

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 :

enter image description here

Upvotes: 7

rattray
rattray

Reputation: 6039

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

Upvotes: 25

anonymous
anonymous

Reputation: 3544

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.

Upvotes: 92

Mr_KeyCode
Mr_KeyCode

Reputation: 31

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;

Upvotes: 1

user4622420
user4622420

Reputation: 21

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.

Upvotes: 2

wteuber
wteuber

Reputation: 1238

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("[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"

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".

Upvotes: 10

Dan King
Dan King

Reputation: 901

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.

Upvotes: 80

Joe L.
Joe L.

Reputation: 4643

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

Upvotes: 5

Sarfraz
Sarfraz

Reputation: 382696

Try this:

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

Upvotes: 2

Related Questions