Extract domain of Email from table in SQL Server

Introduction : As a DBA, you might come across a request where you need to extract the domain of the email address, the email address that is stored in the database table. In case you want to count the most used domain names from email addresses in any given table, you can count the number of extracted domains from Email in SQL Server as shown below. SQL Queries could be used to extract the domain from the Email address. Let us created table named "email_demo" -

create table (ID int, Email varchar (200));

Inserting values in the table email_demo -

insert into email_demo values(
(1, '[email protected]'), (2, '[email protected]'),
(3, '[email protected]'), (4, '[email protected]'),
(5, '[email protected]'), (6, '[email protected]'),
(7, '[email protected]'), (8, '[email protected]'),
(9, '[email protected]'), (10, '[email protected]'),
(11, '[email protected]'), (12, '[email protected]'),
(13, '[email protected]'), (14, '[email protected]'),
(15, '[email protected]'), (16, '[email protected]'),
(17, '[email protected]'));

Displaying the table contents -

select * from email_demo;

ID	Email
1	[email protected]
2	[email protected]
3	[email protected]
4	[email protected]
5	[email protected]
6	[email protected]
7	[email protected]
8	[email protected]
9	[email protected]
10	[email protected]
11	[email protected]
12	[email protected]
13	[email protected]
14	[email protected]
15	[email protected]
16	[email protected]
17	[email protected]

1. Extract Domain From Email in SQL Server : In below example we will use SUBSTRING function to select the string after the @ symbol in each of the value. SQL Extract Domain From Email -

SELECT ID,
SUBSTRING ([Email], CHARINDEX( '@', [Email]) + 1,
LEN([Email])) AS [Domain]
FROM [email_demo];

OUTPUT :

ID	Domain
1	gfg.com
2	gfg.com
3	gfg.org
4	xyz.com
5	xyz.com
6	xyz.com
7	gfg.com
8	gfg.com
9	abc.com
10	mno.com
11	gfg.com
12	abc.com
13	mno.com
14	abc.com
15	xyz.com
16	gfg.org
17	abc.com

Approached used :

• Here we assigned the Source as our Column Name 'Email' in the SUBSTRING function.
• Next, we used the CHARINDEX function to find the @ symbol, and then added 1, so that the starting point will be after the @ symbol.
• Then, we used the LEN function to specify the end value.

2. Count the number of extracted Domain From Email in SQL Server : Approach 1 : SQL Query to Count Number of Records for Extract Domain name From Email -

SELECT RIGHT ([Email],
LEN([Email]) - CHARINDEX( '@', [Email])) AS [Domain],
COUNT(*) AS [Total Number of Domain]
FROM [email_demo]
WHERE LEN([Email ]) > 0
GROUP BY RIGHT([Email],
LEN([Email]) - CHARINDEX( '@', [Email]));

OUTPUT :

Domain	Total Number of Domain
abc.com	4
gfg.com	5
gfg.org	2
mno.com	2
xyz.com	4

Approach 2 : SQL Query to Count Number of Records for Extract Domain name From Email -

SELECT SUBSTRING ([Email],
CHARINDEX( '@', [Email] ) + 1, LEN([Email])) AS [Domain],
COUNT(*) AS [Total Number of Domain]
FROM [email_demo]
WHERE LEN([Email]) > 1
GROUP BY SUBSTRING ([Email], CHARINDEX( '@', [Email] ) + 1,
LEN([Email]));

OUTPUT :

Domain	Total Number of Domain
abc.com	4
gfg.com	5
gfg.org	2
mno.com	2
xyz.com	4