April 30, 2014 at 7:20 am
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
April 30, 2014 at 7:42 am
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.
April 30, 2014 at 8:11 am
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
April 30, 2014 at 8:25 am
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 > 0AND [end1].pos1 > start1.pos1) and (start2.pos2 > 0AND [end2].pos2 > start2.pos2))
April 30, 2014 at 8:54 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply