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
I would like to set the domain formula in a defaut value for a new column … but it seems not to be possible …
Hi There,
I have a requirement: Find and extract email address from Long Text field. The field contains more than 2000 chars. I am looking for ordinary SQL Query (TSQL), so i can use it in Business Intelligence Report.
I can’t use ‘Function’ or any other format.s
I am searching in Google not able to find the right method.
Thanks for your help.
Kiru
Hint:
You can easily turn this query around to give you all the Email Adresses of a given colum, simply change the code a little bit to get the left string part instead of the right string part:
—
SELECT LEFT(Col0, CHARINDEX(‘@’, Col0) ) as EmailColumn, Col0
FROM MyTabe
order by Col0
—-
in asp access database this query not work, someone has the solution, for asp and access database thanks in advance.
SQL = ” SELECT RIGHT(email, LEN(email) – CHARINDEX(‘@’, email)) AS Domain, COUNT(email) AS EmailCount FROM tbUser WHERE (LEN(email) > 0) GROUP BY RIGHT(emailUser, LEN(email) – CHARINDEX(‘@’, email)) ORDER BY EmailCount DESC
This was so helpful, thank you!
Hi, I have solved this in very clear way, no need to long query
Ex.
Select Id, email, right(email, charindex(‘@’, reverse(email))-1) as domain from domain
Here I’d, email is a column name and table name is domain.
Amit pratap
select reverse(substring(reverse(email),1,charindex(‘@’,reverse(email))-1)),count(*) as count
from membersinfo
group by reverse(substring(reverse(email),1,charindex(‘@’,reverse(email))-1))
order by count(*) desc;
Hi Sir can you please simplify my query to get domain name without .com
select email,
left(
replace(
substring(email,charindex(‘@’,email)+1,LEN(Email) – CHARINDEX(‘@’, email)),
right(
substring(email,charindex(‘@’,email)+1,LEN(Email) – CHARINDEX(‘@’, email)),len(
substring(email,charindex(‘.’,email)+1,LEN(Email) – CHARINDEX(‘.’, email))
)),”),
charindex(‘.’,
replace(substring(email,charindex(‘@’,email)+1,LEN(Email) – CHARINDEX(‘@’, email)),
right(
substring(email,charindex(‘@’,email)+1,LEN(Email) – CHARINDEX(‘@’, email)),len(
substring(email,charindex(‘.’,email)+1,LEN(Email) – CHARINDEX(‘.’, email))
)),”)
)-1)
Thanks,
Vani
select substring(‘hemasundar@gmail.com’,charindex(‘@’,’hemasundar@gmail.com’)+1,charindex(‘.,’,hemasundar@gmail.com’)
-charindex(‘@’,’hemasundar@gmail.com’)-1)
—-this method will you to get only domain name from given email
thank you so much indeed nice query works perfect
Yes, thanks this query did exactly what I needed. Pinal Dave is the Man!
I have a column in a SQL table that has 5000 email addresses in it. I created two new columns called Username and Domain. I would like to split that email address at the @ writing into the new column the prefix into Username and the suffix into Domain. I cannot find an example of how to do this. Your help would be appreciated.
You can use LEFT for “@” and get username and use my trick.