Split function in sql server

Posts Tagged ‘Split function in sql server’

SQL SERVER – Selecting Domain from Email Address

Posted in Solutions, SQL SERVER, tagged raresql, Selecting Domain from Email Address, Split function in sql server, SQL on October 27, 2012| Leave a Comment »

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 »

Posted in Solutions, SQL SERVER, tagged cte, Split function in sql server, split string, SQL SERVER – UDF – Function to Convert List to Table, SQL Solutions on February 14, 2012| 9 Comments »

We use split function in our development frequently. The purpose of this function is to split single string having delimiters (comma, semicolon etc.) to convert into multiple strings. Many developers have designed it in different ways.
Given below is the split function, developed with the help of XQuery.
Let me explain this function demonstrating simple example.

Split the @variable:

--Create this user deinfed function.
Create Function dbo.[UDF_Split]
(
@String VARCHAR(MAX),  -- Variable for string
@delimiter VARCHAR(50) -- Delimiter in the string
)
RETURNS @Table TABLE(        --Return type of the function
Splitcolumn VARCHAR(MAX)
)
BEGIN
Declare @Xml AS XML
-- Replace the delimiter to the opeing and closing tag
--to make it an xml document
SET @Xml = cast(('<A>'+replace(@String,@delimiter,'</A><A>')+'</A>') AS XML)
--Query this xml document via xquery to split rows
--and insert it into table to return.
INSERT INTO @Table SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)
RETURN
END
GO
-- For Example :
SELECT * FROM dbo.[UDF_Split] ('Bob,Hulda,Jacoba',',')
-- We can use this function to split table's column as well
--but the issue is, if we have 5 rows in a table
--This function will be executed 5 times.
--I have another solution to split table's column
GO

Split the Table’s Column:

CREATE TABLE [dbo].[Student](
[Student ID]   [varchar] (6) NOT NULL ,
[Student Name] [varchar](50) NOT NULL,
[Code] [varchar] (MAX))
GO
Insert into dbo.[Student] values ('STD001','Bob','E1,E2,E3')
Insert into dbo.[Student] values ('STD002','Alexander','X1,X2,X3')
Insert into dbo.[Student] values ('STD003','Hosanna','A1,A2,A3')
GO
-- Here is the sample query to split table's column.
Declare @delimiter VARCHAR(50)
Set @delimiter=','
;WITH Cte AS
(
SELECT
[Student ID],
[Student Name],
-- Replace the delimiter to the opeing and closing tag
--to make it an xml document
CAST('<M>' + REPLACE([Code], @delimiter , '</M><M>') + '</M>' AS XML) AS [Code]
FROM  [Student]
)
Select
[Student ID],
[Student Name],
--Query this xml document via xquery to split rows
Split.a.value('.', 'VARCHAR(MAX)') AS [Code]
FROM Cte
CROSS APPLY [Code].nodes('/M')Split(a)