Recently I came across a quick need where I needed to retrieve domain of the email address. The email address is in the database table. I quickly wrote following script which will extract the domain and will also count how many email addresses are there with the same domain address.
SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) Domain ,
COUNT(Email) EmailCount
FROMÂ Â dbo.email
WHEREÂ Â LEN(Email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', email))
ORDER BY EmailCount DESC
Above script will select the domain after @ character. Please note, if there is more than one @ character in the email, this script will not work as that email address is already invalid.
Do you have any similar script which can do the same thing efficiently? Please post as a comment.
Reference: Pinal Dave (https://blog.sqlauthority.com)
34 Comments. Leave new
SELECT RIGHT(Email, CHARINDEX(‘@’, REVERSE(email))-1) as Domain ,
COUNT(Email) as EmailCount
FROM dbo.email
WHERE LEN(Email) > 0
GROUP BY RIGHT(Email, CHARINDEX(‘@’, REVERSE(email))-1)
ORDER BY EmailCount DESC
The one Pinal (author) put on here did not work but yours actually did. Thanks!
you can also use the substring function to get the domain part of the email
substring(email, charindex(‘@’, email) + 1, len(email))
Thanks sir, good articles to learn
Cant we use “substring” instead if “right” here ?
like
select substring(Email , CHARINDEX(‘@’, Email )+ 1,len(Email)) Domain ,
count(Email ) EmailCount
from dbo.data
where len(isnull(Email ,”)) > 0
group by substring(Email , CHARINDEX(‘@’, Email )+ 1,len(Email))
order by Emailcount desc
select substring(emailid,charindex(‘@’,emailid)+1,len(emailid)) as Domain,count(emailid) as EmailCount
from email
where len(emailid)>0
group by substring(emailid,charindex(‘@’,emailid)+1,len(emailid))
order by Emailcount desc
Hi;
Mr. Pinaldave
It’s Excellent and very much use full
Thanks a lot
Thanks for share. Good script
good one
Hi Pinal,
Recently I have used the same method for one of my shopping cart project. I was asked to show approx 50 characters for a product description followed by 4 dots and it should not cut last word. So I used the same method. here is my query
SELECT productID, productName, LEFT(productDescription, CHARINDEX(” ”, productDescription, 50))+”….” as productDescription, ….
FROM tbl_Products
Thanks
According to RFC’s 5322 and 5321, a quoted string can be used to allow invalid characters… such that “pinal@home”@sqlauthority.com would be a valid email address. However, it’s rare to see a quoted string used for email.
I would also guess that using “WHERE LEN(email) > 0” is not SARGable, and not using an index if it’s available.
I have something like that. Which was separating the username and domain parts of the address. I am using it in a computed column. See below
(left([EMailAddress],charindex(‘@’,[EMailAddress])-(1)))
and
(right([EMailAddress],len([EMailAddress])-charindex(‘@’,[EMailAddress])))
However I was not able to solve a problem with it crashing when reading the table with invalid email’s which did not contain an @ symbol.
select SUBSTRING(EmailAddress,PATINDEX(‘%@%’,EmailAddress)+1,100),COUNT(1) from Person.Contact
GROUP BY SUBSTRING(EmailAddress,PATINDEX(‘%@%’,EmailAddress)+1,100)
ORDER BY 2 DESC
Try This query.
SELECT ISNULL(SUBSTRING(Email,CHARINDEX(‘@’,isnull(email,”)) + 1,LEN(Email) – CHARINDEX(‘@’,isnull(email,”))),”),COUNT(*) as overallcount
FROM @t
WHERE ISNULL(Email,”) ”
GROUP BY
ISNULL(SUBSTRING(Email,CHARINDEX(‘@’,isnull(email,”)) + 1,LEN(Email) – CHARINDEX(‘@’,isnull(email,”))),”)
ORDER BY
overallcount DESC
Hi Pinal,
With minimal operation I think we can achieve this with below code and it can handle multiple @ scenario
Code:
SELECT
RIGHT(email,CHARINDEX(‘@’,REVERSE(email))-1) AS Domain,
COUNT(*) AS [No]
FROM @t
WHERE len(ISNULL(email,”)) > 1
GROUP BY RIGHT(email,CHARINDEX(‘@’,REVERSE(email))-1)
Hi Pinal,
we need add email validation check in this script because this script is working on @ but some invalid email entered in database table then this scriot is not working as expected.
I have modified it as below.
SELECT RIGHT(Email, LEN(Email) – CHARINDEX(‘@’, email)) Domain ,
COUNT(Email) EmailCount
FROM dbo.email
WHERE LEN(Email) > 0 and CHARINDEX(‘@’, email) > 0
GROUP BY RIGHT(Email, LEN(Email) – CHARINDEX(‘@’, email))
ORDER BY EmailCount DESC
How to get sub string from a string:
input:-
1. /documents/subDocuments/test_123.txt (required result ‘test’)
2. /documents/subDocuments/test_def_45ddd.txt (required result ‘test_def’)
I want the sub string between last index of ‘/’ and last index of ‘_’
Thanks,
Rashmita
SELECT SUBSTRING(‘abc@xyz.com’,CHARINDEX(‘@’,’abc@xyz.com’) + 1,LEN(‘abc@xyz.com’)-CHARINDEX(‘@’,’abc@xyz.com’))
Hi, Thanks for reply.. For MySQL query will be like
SELECT RIGHT(email, length(email) – length(SUBSTRING_INDEX(email,’@’,1))) as Domain, COUNT(email) as Cnt
FROM table
GROUP BY RIGHT(email, length(email) – length(SUBSTRING_INDEX(email,’@’,1)))
ORDER BY Cnt
You can use the below in oracle to retrieve only company name like ‘yahoo’ or ‘gmail’ etc., from email id Hence it will omit .com or .co.in in the end.
Let me know if this is helpful for you guys, your feedback is highly appreciated.
select substr(‘Email’,instr(‘Email’,’@’)+1,(instr(‘Email’,’.c’,-1)-1)-(instr(‘Email’,’@’))) from Table Name