Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Converting / Parsing Delimited string column in SQL to Rows

, 12 Aug 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
How to convert / Parse Delimited string column in SQL to Rows

Have you ever wondered how you would convert that SQL column which is comma delimited into Rows so that you can effectively join it with other tables or use that information better. This might happen for those of you who have saved the keywords let's say of a blog application in a single column delimited with comma, or you might be using an old DotNetNuke version where the RoleIDs are in a ; delimited string saved in one column and you want to use that information in a better and easier way, or for any other reasons that you might need. Well here is an easy solution which you could do in one select, thanks to the XML Data Type functions.

So let's say you have a table like this:

And your data is like this:

And you want to generate a report like this:

Keywords Count
-------- -----------
AA       3
BB       3
CC       2
DD       2
EE       1
FF       3
GG       1
HH       1
JJ       1
KK       1
MM       2
TT       1
UU       1
WW       1
XX       1

(15 row(s) affected)

First you have to select the Keywords, you can already easily view the parsed column by doing this query:

WITH myCommonTblExp AS (
 SELECT CompanyID,
 CAST('<I>' + REPLACE(Keywords, ',', '</I><I>') + '</I>' AS XML) AS Keywords
 FROM Companies
)
SELECT CompanyID, RTRIM(LTRIM(ExtractedCompanyCode.X.value_
	('.', 'VARCHAR(256)'))) AS Keywords
FROM myCommonTblExp
CROSS APPLY Keywords.nodes('//I') ExtractedCompanyCode(X)

Now knowing that you can do that, all you have to do is to group them and count, but you cannot group XML methods so my suggestion is create a view of the query above:

CREATE VIEW [dbo].[DissectedKeywords]
AS
WITH myCommonTblExp AS (
 SELECT
 CAST('<I>' + REPLACE(Keywords, ',', '</I><I>') + '</I>' AS XML) AS Keywords
 FROM Companies
)
SELECT RTRIM(LTRIM(ExtractedCompanyCode.X.value('.', 'VARCHAR(256)'))) AS Keywords
FROM myCommonTblExp
CROSS APPLY Keywords.nodes('//I') ExtractedCompanyCode(X)
GO

and perform your count on that view:

SELECT Keywords, COUNT(*) AS KeyWordCount FROM DissectedKeywords
GROUP BY Keywords
ORDER BY Keywords

See, it's simple!


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141220.1 | Last Updated 12 Aug 2010
Article Copyright 2010 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid