? on Parsing more than one section of data

  • 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

  • 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.

  • 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

  • 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))

  • 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