SQL – Split delimited columns using XML Or UDF function

The requirement is to convert the delimited column into rows

Input data and required output details are given below

Eno Ename Esloc EDept
1 ABC NJ 10,20,30,40,50

Output:-

Eno Ename Esloc EDept
1 ABC NJ 10
1 ABC NJ 20
1 ABC NJ 30
1 ABC NJ 40
1 ABC NJ 50

Download the Script Split Delimited Column using XML Or UDF

T-SQL -Split a delimited column data using XML

DECLARE @DemoTable table
 (
 Eno int,
 Ename char(10),
 EsLoc char(10),
 EDept varchar(20))
 
INSERT INTO @DemoTable values(1,'Prashanth','NJ','1,2,3,4,5')
INSERT INTO @DemoTable values(2,'Jayaram','NY','1,2,3')

SELECT A.ENo, A.EName , A.EsLoc,
 Split.a.value('.', 'VARCHAR(100)') AS Dept 
 FROM (SELECT ENo,EName,EsLoc,
 CAST ('<M>' + REPLACE(EDept, ',', '</M><M>') + '</M>' AS XML) AS String 
 FROM @DemoTable) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

XML

OR

Using UDF-

Reference Link :- http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html 

CREATE FUNCTION ParseValues
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(8000))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

DECLARE @DemoTable table
 (
 Eno int,
 Ename char(10),
 EsLoc char(10),
 EDept varchar(20))
 
INSERT INTO @DemoTable values(1,'Prashanth','NJ','1,2,3,4,5')
INSERT INTO @DemoTable values(2,'Jayaram','NY','1,2,3')

SELECT t.Eno, t.Ename,t.EsLoc,f.Val Dept
FROM @DemoTable t
CROSS APPLY dbo.ParseValues(t.EDept,',')f

Output:-

XML-1

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL, T-SQL and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s