SQL Server Portal

Today, I received an email from our marketing team that they have a databank of email addresses and they wish to get the domain name from the email addresses along with total number of domains.
So, I started working on it and I did it using Substring function. But I thought of doing it with multiple string functions.
In all examples, I used CHARINDEX but you can also use PATINDEX like PATINDEX ( ‘%@%’,[Email Address])

Let me explain it with simple examples.

Create table tbl_sample
(
[Email Address] varchar(max)
)
Insert into tbl_sample values ('info@microsoft.com')
Insert into tbl_sample values ('Contact@microsoft.com')
Insert into tbl_sample values ('Support@raresql.com')
Insert into tbl_sample values ('info@raresql.com')
Insert into tbl_sample values ('Queries@raresql.com')

Method 1: With the help of Substring Function

Select Substring([Email Address],CharIndex('@',[Email Address])+1 ,LEN([Email Address])) as [Domain Name]
,Count(*) as [Total No of domains]
from tbl_Sample
Where LEN([Email Address])>1
Group By Substring([Email Address],CharIndex('@',[Email Address])+1 ,LEN([Email Address]))

Method 2: With the help of Stuff Function

Select STUFF([Email Address],1,CharIndex('@',[Email Address]) ,'') as [Domain Name]
,Count(*) as [Total No of domains]
from tbl_Sample
Group By STUFF([Email Address],1,CharIndex('@',[Email Address]) ,'')

Method 3: With the help of Right Function

Select Right([Email Address],LEN([Email Address])-CharIndex('@',[Email Address])) as [Domain Name]
,Count(*) as [Total No of domains]
from tbl_Sample
Where LEN([Email Address])>1
Group By Right([Email Address],LEN([Email Address])-CharIndex('@',[Email Address]))

Method 4: With the help of Left Function

Select Reverse(Left(REVERSE([Email Address]),CharIndex('@',REVERSE([Email Address]))-1)) as [Domain Name]
,Count(*) as [Total No of domains]
from tbl_Sample
Where LEN([Email Address])>1
Group By Reverse(Left(REVERSE([Email Address]),CharIndex('@',REVERSE([Email Address]))-1))

Method 5: With the help of XQuery

Declare @delimiter VARCHAR(50)
Set @delimiter='@'

;WITH Cte AS
(
SELECT CAST('<FirstPart>' + REPLACE([Email Address], @delimiter , '</FirstPart><SecondPart>') + '</SecondPart>' AS XML) AS [Email Address]
FROM  tbl_sample
)
,CTE2 AS (Select Split.a.value('(.)', 'VARCHAR(MAX)') AS [Email Address]
FROM Cte
CROSS APPLY [Email Address].nodes('/SecondPart')Split(a))

Select [Email Address]
,Count(*) as [Total No of domains]
from CTE2
Group By [Email Address]

Result from all above queries :

Read Full Post »