Show Only the Domain of an Email Address in SQL Server

When working with email data in SQL Server, you may often need to show only the domain of an email address. Extracting email domains is useful for analyzing trends, identifying invalid emails, or finding the most frequent domains in your dataset.

In this guide, I’ll demonstrate how to create a sample table, insert test data, and run SQL queries to extract and analyze email domains.

Step 1: Setting Up Test Data

To get started, run the following script to create a sample database and populate it with test email data:

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'),
('john','mcdonald','admin','johnman64@gmail.com'),
('alex','thompson','member','stickman22@hotmail.co.uk'),
('fiona','thompson','member','stickmanswife@hotmail.co.uk'),
('mo','molsen','member','mo3@outlook.com'),
('desire','nicholson','member','nicholsons.desire@outlook.com'),
('don','donaldson','member','thedon@outlook.com');

Step 2: Extracting Email Domains

Query 1: Show Distinct Email Domains
To extract unique email domains from the table, use this query:

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

The RIGHT function extracts the portion of the email address after the @ symbol, and CHARINDEX finds the position of '@‘.

Query 2: Count Email Domains
If you want to count the occurrences of each domain, use this query:

-- 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;

Sorting by domain_count DESC helps identify the most frequent email domains quickly.

Hope this helps!