Need to Parse a column.

Question

Need to Parse a column.

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

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

Luis Cazares SSC Guru Points: 183706 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: 63621 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: 182951 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