Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

? on Parsing more than one section of data Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 7:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
Hi

I have some code I used to parse out a section of data in a field below.

Now I need parse several sections of the same field
for example below parses T_400 thru T_501 as ss

Now I need to parse T_400 thru T_501 as ss1
T_401 thru T_502 as ss2
T_402 thru T_503 as ss3 .... etc..

I have tried stringing them along as pos1, pos2 etc
I think I'm getting confused in the start and end sections..

The data looks similar to this (I shortened it becuase its too long
(@@T_400_ = "test goes here... "@@T_501_ = "More text... "@@T_401_ = "Even more.....""@@T_502_"etc...


Thanks In Advance
Joe


SELECT
ASSESSMENT_DATA,
start.pos,
[end].pos,
SUBSTRING(ASSESSMENT_DATA,start.pos,[end].pos-start.pos) as ss
FROM USER_DEFINED_DATA
CROSS APPLY (SELECT pos = LEN('@@T_400_ = "') + CHARINDEX('@@T_400_ = ',ASSESSMENT_DATA,1)) start
CROSS APPLY (SELECT pos = CHARINDEX('"@@T_501',assessment_data,start.pos)) [end]
WHERE ASSESSMENT_MONIKER = '4D17FC8EFA484E8B884E5F1C9B3D55DB'
AND ASSESSMENT_DATA IS NOT NULL
AND start.pos > 0
AND [end].pos > start.pos

Post #1566383
Posted Wednesday, April 30, 2014 7:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
How many groups (ssn) can you have? Is it reasonable to build the parsing inline or maybe break it up into a temp table based on the sections beginning with "@@T_nnn_"? Suppose you did that and put the results into a temp table with columns Tnnn (containing the @@T number) and Text (containing the text after the = sign.) Then, I think your job might be easier.

See this article:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Work through the examples, then apply it to break up your input. (HINT, change the @@T to a single character (maybe a tab?) before using the parser). Then you'll have results in a nice table to work with.
Post #1566398
Posted Wednesday, April 30, 2014 8:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
So there are 8 section of the field I need to parse

I found that this(below) "Sort of" works

Except I am getting the error "Invalid length parameter passed to the LEFT or SUBSTRING function."

I assume that one of the substrings is null

Is there a way to use a nullif or similar, to put a null in the field to avoid the error?




SELECT
ASSESSMENT_DATA,
start1.pos1,
[end1].pos1,
SUBSTRING(ASSESSMENT_DATA,start1.pos1,[end1].pos1-start1.pos1) as ss1,

SUBSTRING(ASSESSMENT_DATA,start2.pos2,[end2].pos2-start2.pos2) as ss2


FROM USER_DEFINED_DATA
CROSS APPLY (SELECT pos1 = LEN('@@T_400_ = "') + CHARINDEX('@@T_400_ = ',ASSESSMENT_DATA,1)) start1
CROSS APPLY (SELECT pos1 = CHARINDEX('"@@T_501',assessment_data,start1.pos1)) [end1]

CROSS APPLY (SELECT pos2 = LEN('@@T_401_ = "') + CHARINDEX('@@T_401_ = ',ASSESSMENT_DATA,1)) start2
CROSS APPLY (SELECT pos2 = CHARINDEX('"@@T_502',assessment_data,start2.pos2)) [end2]

WHERE ASSESSMENT_MONIKER = '4D17FC8EFA484E8B884E5F1C9B3D55DB'
AND ASSESSMENT_DATA IS NOT NULL
Post #1566416
Posted Wednesday, April 30, 2014 8:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
So I tested with adding the last line in the where clause to get only valid start and end position, and it worked but of course I am missing data where ss1 or ss2 do not exist

I Assume I am getting a negative position ?



SELECT
ASSESSMENT_DATA,
start1.pos1,
[end1].pos1,
nullif(SUBSTRING(ASSESSMENT_DATA,start1.pos1,[end1].pos1-start1.pos1),'') as ss1,

nullif(SUBSTRING(ASSESSMENT_DATA,start2.pos2,[end2].pos2-start2.pos2),'') as ss2


FROM USER_DEFINED_DATA
CROSS APPLY (SELECT pos1 = LEN('@@T_400_ = "') + CHARINDEX('@@T_400_ = ',ASSESSMENT_DATA,1)) start1
CROSS APPLY (SELECT pos1 = CHARINDEX('"@@T_501',assessment_data,start1.pos1)) [end1]

CROSS APPLY (SELECT pos2 = LEN('@@T_401_ = "') + CHARINDEX('@@T_401_ = ',ASSESSMENT_DATA,1)) start2
CROSS APPLY (SELECT pos2 = CHARINDEX('"@@T_502',assessment_data,start2.pos2)) [end2]

WHERE ASSESSMENT_MONIKER = '4D17FC8EFA484E8B884E5F1C9B3D55DB'
AND ASSESSMENT_DATA IS NOT NULL
AND (start1.pos1 > 0 AND [end1].pos1 > start1.pos1) and (start2.pos2 > 0 AND [end2].pos2 > start2.pos2))
Post #1566424
Posted Wednesday, April 30, 2014 8:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
So I've been playing with this and below works
Not sure if its the best way .....
But I'll try to incorporate it


SELECT
ASSESSMENT_DATA,
start1.pos1,
[end1].pos1,start2.pos2,
[end2].pos2,
case when [end1].pos1 >1 then
SUBSTRING(ASSESSMENT_DATA,start1.pos1,[end1].pos1-start1.pos1)
else '' end as ss1,
case when [end2].pos2 > 1 then
SUBSTRING(ASSESSMENT_DATA,start2.pos2,[end2].pos2-start2.pos2)
else '' end as ss2

FROM USER_DEFINED_DATA

CROSS APPLY (SELECT pos1 = LEN('@@T_400_ = "') + CHARINDEX('@@T_400_ = ',ASSESSMENT_DATA,1)) start1
CROSS APPLY (SELECT pos1 = CHARINDEX('"@@T_501',assessment_data,start1.pos1)) [end1]
CROSS APPLY (SELECT pos2 = LEN('@@T_401_ = "') + CHARINDEX('@@T_401_ = ',ASSESSMENT_DATA,1)) start2
CROSS APPLY (SELECT pos2 = CHARINDEX('"@@T_502',assessment_data,start2.pos2)) [end2]

WHERE ASSESSMENT_MONIKER = '4D17FC8EFA484E8B884E5F1C9B3D55DB'
AND ASSESSMENT_DATA IS NOT NULL

Post #1566435
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse