Systems Engineering and RDBMS

Parsing a de-limited string of values

Posted by decipherinfosys on January 21, 2009

Today, we had a need to write a SQL block as were migrating the database of a client from their legacy version to the new version.  In their older system since they had done a direct copy of the system from their Unix/Cobol based system, there was a field that contained proposal codes for the different proposal ID (Primary Key) values.  The data in question was something like this:

declare @proposal_master table (proposal_id int primary key, proposal_code nvarchar(100));
insert into @proposal_master values (1, ’10|20|30|40|50|43|54′);
insert into @proposal_master values (2, ’15|25|30|35|45′);
insert into @proposal_master values (3, ’11|12|19|24|35|41′);

There are several ways write the code to get this in a normalized fashion including doing pivoting to convert the column data from a row to columns.  Here is another quick way to do it by using XML and a CTE:

Let’s first cast it to XML by replacing the delimiter and creating a XML structure:

CAST(‘<codes><pccode>’ + REPLACE(proposal_code, ‘|’, ‘</pccode><pccode>’) + ‘</pccode></codes>’ AS XML) AS Proposal_Codes
FROM @proposal_master

So, by doing this, we have essentially now got our data in this format (showing the data for proposa_master_id = 1):


And now, all we need to do is use a CTE and use the nodes() method to pivot it out – here is how we can go about doing it:

;WITH codes AS (
CAST(‘<pccode>’ + REPLACE(proposal_code, ‘|’, ‘</pccode><pccode>’) + ‘</pccode>’ AS XML) AS Proposal_Codes
FROM @proposal_master
pc.i.value(‘.’, ‘NVARCHAR(5)’) AS Proposal_Code
FROM codes
CROSS APPLY Proposal_Codes.nodes(‘//pccode’) pc(i)

In the above code, we are using three main things in order to achieve our aim: CTE, casting as an XML & using the nodes() method and CROSS APPLY operator.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: