Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Extract domain of Email from table in SQL Server

  • Last Updated : 07 Dec, 2020

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” –

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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

Inserting values in the table email_demo –

insert into email_demo values(
(1, 'Sam@gfg.com'), (2, 'Khushi@gfg.com'),
(3, 'Komal@gfg.org'), (4, 'Priya@xyz.com'),
(5, 'Sam@xyz.com'), (6, 'Krish@xyz.com'),
(7, 'Neha@gfg.com'), (8, 'Pam@gfg.com'),
(9, 'Mohan@abc.com'), (10, 'Ankit@mno.com'),
(11, 'Kumari@gfg.com'), (12, 'Hina@abc.com'),
(13, 'Jaya@mno.com'), (14, 'Piyush@abc.com'),
(15, 'Khushi@xyz.com'), (16, 'Mona@gfg.org'),
(17, 'Roza@abc.com'));

Displaying the table contents –



select * from email_demo;
IDEmail
1Sam@gfg.com
2Khushi@gfg.com
3Komal@gfg.org
4Priya@xyz.com
5Sam@xyz.com
6Krish@xyz.com
7Neha@gfg.com
8Pam@gfg.com
9Mohan@abc.com
10Ankit@mno.com
11Kumari@gfg.com
12Hina@abc.com
13Jaya@mno.com
14Piyush@abc.com
15Khushi@xyz.com
16Mona@gfg.org
17Roza@abc.com

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 :

IDDomain
1gfg.com
2gfg.com
3gfg.org
4xyz.com
5xyz.com
6xyz.com
7gfg.com
8gfg.com
9abc.com
10mno.com
11gfg.com
12abc.com
13mno.com
14abc.com
15xyz.com
16gfg.org
17abc.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 :

DomainTotal Number of Domain
abc.com4
gfg.com5
gfg.org2
mno.com2
xyz.com4

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 :

DomainTotal Number of Domain
abc.com4
gfg.com5
gfg.org2
mno.com2
xyz.com4
My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!