SQL DBA Blog

When performing data analysis of email data in SQL, there’s often a need to show only the domain of an email address. You can use this for things like checking invalid emails or to find out your most populous domains.

In this blog post, I’ll show how to get this information with some queries.

The following script will create a database & table with test data in the table that we can use for a quick demo:

USE master
GO

DROP DATABASE IF EXISTS fatanta; 

CREATE DATABASE fatanta;

USE fatanta;
GO

DROP TABLE IF EXISTS players;
CREATE TABLE players (
	id INT IDENTITY PRIMARY KEY NOT NULL, 
	first_name VARCHAR(100),
	surname VARCHAR(100),
	user_role VARCHAR(40),
	email_address VARCHAR(200)
)

INSERT INTO players VALUES ('peter','whyte','master','pete_test_mail@hotmail.com')
INSERT INTO players VALUES ('john','mcdonald','admin','johnman64@gmail.com')
INSERT INTO players VALUES ('alex','thompson','member','stickman22@hotmail.co.uk')
INSERT INTO players VALUES ('fiona','thompson','member','stickmanswife@hotmail.co.uk')
INSERT INTO players VALUES ('mo','molsen','member','mo3@outlook.com')
INSERT INTO players VALUES ('desire','nicholson','member','nicholsons.desire@outlook.com')
INSERT INTO players VALUES ('don','donaldson','member','thedon@outlook.com')

Once that’s been run we can straight into selecting the data and aggregate email addresses for analysis.

This query will show the distinct email domains within a table:

-- Show distinct email domains. 
SELECT DISTINCT
	RIGHT(email_address, LEN(email_address) - CHARINDEX('@', email_address)) AS [email_domain]
FROM players
ORDER BY 1;

And this query will do the same but includes a count on email domains that are the same:

-- Show distinct email domains with counts.
SELECT 
	RIGHT(email_address, LEN(email_address) - CHARINDEX('@', email_address)) AS [email_domain],
	COUNT(*) AS [count]
FROM players
GROUP BY RIGHT(email_address, LEN(email_address) - CHARINDEX('@', email_address))
ORDER BY [count] DESC;

That’s it on this – hope it’s useful for you, my dear random visitor.