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:

SELECT
proposal_master_id,
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):

<codes>
<pccode>10</pccode>
<pccode>20</pccode>
<pccode>30</pccode>
<pccode>40</pccode>
<pccode>50</pccode>
<pccode>43</pccode>
<pccode>54</pccode>
</codes>

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 (
SELECT
proposal_master_id,
CAST(‘<pccode>’ + REPLACE(proposal_code, ‘|’, ‘</pccode><pccode>’) + ‘</pccode>’ AS XML) AS Proposal_Codes
FROM @proposal_master
)
SELECT
proposal_master_id,
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.

About these ads

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

 
Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: