SQL Retrieve Email Domains | VirtualSpecies

SQL Retrieve Email Domains

If we need to retrieve all the email domains from a SQL table, we can easily pull up result using below script. The SUBSTRING function is used to extract and display the part of a string. In this case, we have retrieved email domains after the @ symbol in each record.

1
2
3
SELECT DISTINCT 
SUBSTRING(Email, CHARINDEX('@',Email) + 1, LEN(Email) - CHARINDEX('@',Email)) 
FROM dbo.EmailRecord
Even better with the script below, we will be able to count how many of each domains are existed in the given table.
1
2
3
SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', Email)) Domain, COUNT(Email) EmailCount
FROM dbo.EmailRecord WHERE LEN(Email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', Email))

No comments:

Post a Comment