SQL Extract Domain From Email

How to write a SQL Query to extract the domain from the Email address with example. For this Interview Question example, we use the below data

SQL Extract Domain From Email Example

The SUBSTRING function allows you to extract and display the part of a string. In this SQL Server example, we will show you how to Select string after the @ symbol in each record.

-- SQL Extract Domain From Email


SELECT SUBSTRING (
		   [Email Adress],
		   CHARINDEX( '@', [Email Adress] ) + 1,
		   LEN([Email Adress])
		 ) AS [Domain Name]
FROM [EmailAdress]

In general, the SUBSTRING Function allows three parameters, and they are Source, start point, endpoint. Here we assigned the

• Source as our Column Name
• Next, we used the CHARINDEX Function to find the @ symbol, and then we added 1. so that the starting point will be after the @ symbol.
• Lastly, we used the LEN Function in SQL Server to specify the end value

SUBSTRING (
	    [Email Adress], -- Source
	    CHARINDEX( '@', [Email Adress] ) + 1, -- Start Point
	    LEN([Email Adress] -- End Point
	  )

SQL Extract Domain From Email and Count

Although the above example will return the result, we want to count the number of records for each domain name. Here, we used the Group By Clause to group the similar domain names. And then, we used the COUNT function to count the number of records in each group.

-- SQL Query to Extract Domain name From Email and Count Number of Records

SELECT SUBSTRING (
		   [Email Adress],
		   CHARINDEX( '@', [Email Adress] ) + 1,
		   LEN([Email Adress])
		 ) AS [Domain Name],
	COUNT(*) AS [Total Records with this Domain]
FROM [EmailAdress]
WHERE LEN([Email Adress]) > 1
GROUP BY SUBSTRING (
	  	     [Email Adress],
		     CHARINDEX( '@', [Email Adress] ) + 1,
		     LEN([Email Adress])
		    )

Extract Domain From Email Example 2

We show how to use the Right Function to extract the domain name from the email address.

-- SQL Query to Extract Domain name From Email and Count Number of Records

SELECT RIGHT (
		[Email Adress],
		LEN([Email Adress]) - CHARINDEX( '@', [Email Adress] )
	     ) AS [Domain Name],
	COUNT(*) AS [Total Records with this Domain]
FROM [EmailAdress]
WHERE LEN([Email Adress]) > 0
GROUP BY RIGHT (
		 [Email Adress],
		 LEN([Email Adress]) - CHARINDEX( '@', [Email Adress] )
	        )