Luis Cazares (8/18/2014)

---

I hope that this is part of a data cleansing process because it's complicated and won't be great for performance.

Here's a possible solution. Be sure to understand it. It uses DelimitedSplit8k function which is explained in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Ask any questions that you have about it.

DECLARE @Sample Table(

string varchar(8000)

)

INSERT @Sample

VALUES('<TV<MR1#4.0#true#2.0#USD>VT>,<TV<MR2#3.0#true#1.5#USD>VT>,<TV<MR3#0.0#true#0.0#USD>VT>,<TV<MR4#0.375#true#0.19#USD>VT>')

SELECT SUBSTRING( d.Item, 5, 3),

REPLACE( PARSENAME(t.dotted, 4), CHAR(7), '.'),

REPLACE( PARSENAME(t.dotted, 3), CHAR(7), '.'),

REPLACE( PARSENAME(t.dotted, 2), CHAR(7), '.'),

REPLACE( PARSENAME(t.dotted, 1), CHAR(7), '.')

FROM @Sample s

CROSS APPLY DelimitedSplit8K( s.string, ',') d

CROSS APPLY (SELECT REPLACE( REPLACE( REPLACE( STUFF( Item, 1, CHARINDEX('#', d.Item), ''), '>VT>', ''), '.', CHAR(7)), '#', '.')) t(dotted)