Need to Parse a column.

Question

Need to Parse a column.

rk07

SSC Enthusiast

Points: 156
More actions
August 18, 2014 at 10:09 am

#310823

Hi,
Stuck with a problem and need help in resolving it. I have a column with the following information in it. I need to split the column and parse the values. Need healp in doing that. Never came situation like this before . I have Parsed one single values from but, not multiple times.
<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>
MR1 -Model Code,
4.0 - Percentage,
true - isAbs,
2.0 - AppliedValue --> This is Actual amount applied as tax.
USD - Currency
For the example provide , the totalTaxValue would be 2.0 + 1.5+0.0+0.19 = 3.69
Thanks in Advance!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rk07 SSC Enthusiast Points: 156 More actions · Answer 2

Thanks Luis . It just a pet project i am working on and need suggestions on which i was stuck.

yingchai SSCommitted Points: 1669 More actions · Answer 3

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)

I received and error -" Invalid object name 'DelimitedSplit8K' " when executing the query. I am using SQL Server 2012.

pietlinden SSC Guru Points: 63434 More actions · Answer 4

You're missing the DelimitedSplit8K function, which is here[/url]. If you read all the way to the bottom, the code is in one of the downloads at the bottom of the article.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

Quick solution, more a POC than anything else.

😎

USE tempdb;

GO

DECLARE @TSTR NVARCHAR(200) = N'<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>';

DECLARE @SQL_STR NVARCHAR(MAX) = N''

SELECT @SQL_STR = N'select [Model Code],[Persentage],[isAbs],[AppliedValue],[Currency] from (values '

+ REPLACE(REPLACE(REPLACE(@TSTR,N'<TV<',N'('''),N'>VT>',N''')'),N'#',N''',''') + N') as X([Model Code],[Persentage],[isAbs],[AppliedValue],[Currency]);';

EXEC (@SQL_STR);

Results

Model Code Persentage isAbs AppliedValue Currency

---------- ---------- ----- ------------ --------

MR1 4.0 true 2.0 USD

MR2 3.0 true 1.5 USD

MR3 0.0 true 0.0 USD

MR4 0.375 true 0.19 USD