Click here to Skip to main content
15,867,594 members
Articles / Web Development / ASP.NET
Tip/Trick

Generating and Splitting a Delimited String Column

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
17 Jan 2013CPOL3 min read 45K   94   7   3
This article describes the easiest way to create and process delimited row using SQL XQuery.

Introduction

Recently I answered one of The Code Project questions. In brief the question was, how to split the delimited string and return as row values. This is one of the common questions we all are facing in our development. Not only splitting the delimited string, but we also struggle when creating the delimited strings using SQL.

In this article, I will show you how easily this can be done by using the SQL XQuery.

Using the Code

Creating Delimited Strings

Suppose we have a table called DeveloperProject to store the individual developer project list.

SQL
DECLARE @DeveloperProject Table(
    DeveoperID INT,
    ProjectCode  VARCHAR(20)
)
 
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'CODE_PROJECT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'MSFT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'REPT_MOD')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(2,'MSFT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(2,'CODE_PROJECT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(3,'REPT_MOD') 

Now we need to convert this as follows:

DeveoperID  ProjectCodes
----------- ------------------------
1           CODE_PROJECT,MSFT,REPT_MOD
2           MSFT,CODE_PROJECT
3           REPT_MOD  

There are many ways to do this. But here is the easiest way.

SQL
SELECT DeveoperID,
    REPLACE((SELECT ProjectCode AS'data()'
        FROM @DeveloperProject dp2 
        WHERE dp2.DeveoperID = dp1.DeveoperID
    FOR XML PATH('')),' ',',')AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID  

Instead of REPLACE function, you can also use the SUBSTRING function as below:

SQL
SELECT DeveoperID,
    SUBSTRING((SELECT ','+ ProjectCode
        FROM @DeveloperProject dp2 
        WHERE dp2.DeveoperID = dp1.DeveoperID
    FOR XML PATH('')), 2, 8000)AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID    
Image 1

FOR XML() clause

A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query.

data() function

If you want to get the value of an attribute, rather than the attribute node itself, we can use the data() method.

For example: If you execute the following query, you will get the xml output with <ProjectCode> attribute nodes.

SQL
SELECT ProjectCode 
FROM @DeveloperProject c2      
FOR XML PATH('')  

Image 2

Now run it as follows, and see the result set. It gives the output without <ProjectCode> nodes.

SQL
SELECT ProjectCode AS 'data()'
FROM @DeveloperProject c2      
FOR XML PATH('')  

Image 3

Splitting Delimited String

Suppose we have a table called DeveloperProjectCSV to store the developer id and comma separated project list:

SQL
DECLARE @DeveloperProjectCSV Table(
    DeveoperID INT,
    ProjectCodes  VARCHAR(MAX)
)

We can use the previously created script to insert the data to this table.

SQL
INSERT INTO @DeveloperProjectCSV(DeveoperID,ProjectCodes)
SELECT DeveoperID,
    REPLACE((SELECT 
        ProjectCode AS 'data()'
     FROM @DeveloperProject dp2 
     WHERE dp2.DeveoperID = dp1.DeveoperID
     FOR XML PATH('')), ' ', ',') AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID   

Now the table contains the following data:

DeveoperID  ProjectCodes
----------- ------------------------------
1           CODE_PROJECT,MSFT,REPT_MOD
2           MSFT,CODE_PROJECT
3           REPT_MOD

And we need to split the project codes for each developer.

SQL
;WITH cte AS (
    SELECT 
        DeveoperID,
        CAST('<r>' + REPLACE(ProjectCodes, ',', '</r><r>') + '</r>' AS XML) AS ProjectCodes
    FROM @DeveloperProjectCSV
)
SELECT 
    DeveoperID,
    xTable.xColumn.value('.', 'VARCHAR(MAX)') AS ProjectCode
FROM cte
CROSS APPLY ProjectCodes.nodes('//r') AS xTable(xColumn) 

Image 4

WITH (common_table_expression)

WITH is used to specify the temporary named result set. In the above, WITH block creates temporary results set with two columns. One is a DeveloperID and other one is an xml type column for ProjectCodes.

nodes() Method

The nodes() method is useful when you want to share an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.

nodes (XQuery) as Table(Column)

XQuery

Is a string literal, an XQuery expression. If the query expression constructs nodes, these constructed nodes are exposed in the resulting rowset. If the query expression results in an empty sequence, the rowset will be empty. If the query expression statically results in a sequence that contains atomic values instead of nodes, a static error is raised.

Table(Column)

Is the table name and the column name for the resulting rowset.

CROSS APPLY

CROSS APPLY is just like an inner join query clause which allows joining between two table expressions.


Creating Delimited Strings with SQL 2000

Since SQL 2000 doesn’t support XML PATH, the above query doesn’t work with SQL 2000. As an alternative option, we can use COALESCE or ISNULL function and create User Defined Function to get the concatenation output.

Suppose we have a table called “DeveloperProject” with the following data inserted.

 DeveloperID ProjectCode
----------- --------------------
1           CODE_PROJECT
1           MSFT
1           REPT_MOD
2           MSFT
2           CODE_PROJECT
3           REPT_MOD 

First, we have to create the concatenation function:

SQL
CREATE FUNCTION dbo.rowToCSV(@DeveoperID int )
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @ProjectCodes VARCHAR(MAX)
    SELECT @ProjectCodes = COALESCE(@ProjectCodes + ', ', '') + ProjectCode
    FROM DeveloperProject
    WHERE DeveoperID = @DeveoperID 
    RETURN @ProjectCodes
END

By using the above function, you will can get the same output.

SQL
SELECT 
DeveoperID, 
ProjectCodes = dbo.rowToCSV(DeveoperID ) 
FROM DeveloperProject 
GROUP BY DeveoperID

Image 5

COALESCE and ISNULL

COALESCE returns the first nonnull expression among its arguments. This is just like ISNULL function in SQL. Both ISNULL and COALESCE can be used to get the same results but there are some differences. 

Splitting Delimited String with SQL 2000

Again, SQL 2000 doesn't support WITH (Common table expression) and the APPLY operators. As an alternative option, we can use the spt_values master table.

SQL
SELECT  a.DeveloperID,
SUBSTRING(',' + a.ProjectCodes + ',', n.Number + 1, 
	CHARINDEX(',', ',' + a.ProjectCodes + ',', n.Number + 1) - n.Number - 1) AS [Value]
FROM DeveloperProjects AS a
INNER JOIN master..spt_values AS n ON SUBSTRING(',' + a.ProjectCodes + ',', n.Number, 1) = ','
WHERE n.Type = 'p'
    AND n.Number > 0 
    AND n.Number < LEN(',' + a.ProjectCodes + ',')

spt_values

spt_values is an undocumented system table, which contains various data items used by Microsoft written stored procedures. We can use the rows with type = 'P' to have a numbers table with the numbers from 1-2047.

License

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


Written By
Technical Lead Eyepax IT Consulting (Pvt) Ltd.
Sri Lanka Sri Lanka
Having more than 9 year hands-on industry experience in software development
Responsible for designing, implementing and managing complex software systems with stringent up-time requirement.

Visit my blog

Comments and Discussions

 
QuestionUsing this with an Insert Statement Pin
Member 141709394-Mar-19 11:33
Member 141709394-Mar-19 11:33 
GeneralMy vote of 5 Pin
arid0813-Jan-13 23:05
arid0813-Jan-13 23:05 
GeneralRe: My vote of 5 Pin
Tharaka MTR14-Jan-13 1:37
professionalTharaka MTR14-Jan-13 1:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.