parsing data in the middle of a field??

Question

parsing data in the middle of a field??

jbalbo

SSCertifiable

Points: 7583
More actions
July 17, 2013 at 7:01 am

#296486

Hi
I have a field with a data string similar to
""....@@T_700_ = "text goes here"@@T_301_ = "06/15/2013"@@T_069_ =.....
I need to pickup what between @@T_700_ = " and "@@T_301_ so in the above I end up with text goes here
Thanks
Joe

Viewing 12 posts - 1 through 11 (of 11 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Can you post a dozen or so sample strings please Joe? Cheers.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

sure the data is always the same

field is called assessment_data

starts with "@@"@@T_001_Version = "v2012.08.10.1_1.7"@@T_002_ = ".......

then at @@T_700_ = "Client Name here"@@T_301_ = "03/22/2013"@@T_069_ = "1"......... thru @@T_850

looking thru the data it is always preceeded by ""@@T_727_ = " and followed by "@@T_301_ =

the only thing I found is sometimes the entire field assessment_data is null

Does that help?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Not really. Like this:

CREATE TABLE #SAMPLE (MyString VARCHAR(200))

INSERT INTO #SAMPLE (MyString)

SELECT 'A whole string from my table' UNION ALL

SELECT 'A second whole string from my table' UNION ALL

SELECT 'A third whole string from my table' UNION ALL

SELECT 'A fourth whole string from my table'

Samples of the whole strings give us context information, without this it's impossible to decide which method to use.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

Thanks for getting back..

The field is huge it contains data from an html form and I need whats in quotes after @@T_700.

Here is the formula i use in crystal and wondering if there was an equiviant way I could do it in sql ..

stringvar x;

numbervar start;

numbervar end;

x:={USER_DEFINED_DATA.ASSESSMENT_DATA};

start:= instr(x,'@@T_700_ = ') + 12;

end := instr(x,'"@@T_301');

x:=mid(x,start,end-start);

x

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Ah, ok.

DROP TABLE #SAMPLE

CREATE TABLE #SAMPLE (MyString VARCHAR(200))

INSERT INTO #SAMPLE (MyString)

SELECT '...@@T_700_ = "v2012.08.10.1_1.7"@@T_301_ = "...' UNION ALL

SELECT '...@@T_700_ = "Client Name here"@@T_301_ = "03/22/2013"@@T_069_ = "1"......... thru @@T_850...'

SELECT

MyString,

start.pos,

[end].pos,

SUBSTRING(MyString,start.pos,[end].pos-start.pos)

FROM #SAMPLE

CROSS APPLY (SELECT pos = LEN('@@T_700_ = ') + CHARINDEX('@@T_700_ = ',MyString,1)) start

CROSS APPLY (SELECT pos = 1+CHARINDEX('"@@T_301',MyString,start.pos)) [end]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jbalbo SSCertifiable Points: 7583 More actions · Answer 6

Thanks that looks great,

Can I do something like this?

SELECT

ASSESSMENT_DATA,

start.pos,

[end].pos,

SUBSTRING(ASSESSMENT_DATA,start.pos,[end].pos-start.pos)

FROM USER_DEFINED_DATA

CROSS APPLY (SELECT pos = LEN('@@T_700_ = ') + CHARINDEX('@@T_700_ = ',ASSESSMENT_DATA,1)) start

CROSS APPLY (SELECT pos = 1+CHARINDEX('"@@T_301',assessment_data,start.pos)) [end]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

It looks ok to me, Joe. Have you tried it yet?

You might benefit from filtering out rows where the column is empty.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jbalbo SSCertifiable Points: 7583 More actions · Answer 8

Hi Chris,

yes tried like this ..

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_700_ = ') + CHARINDEX('@@T_700_ = ',ASSESSMENT_DATA,1)) start

CROSS APPLY (SELECT pos = 1+CHARINDEX('"@@T_301',assessment_data,start.pos)) [end]

WHERE ASSESSMENT_MONIKER = '09D27E579F9344A1BBD442E70CA1241F' AND ASSESSMENT_DATA IS NOT NULL

Getting this error after seeing some data..

Invalid length parameter passed to the LEFT or SUBSTRING function.

I assume I have some null field at @@T_700?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Try this, 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_700_ = ') + CHARINDEX('@@T_700_ = ',ASSESSMENT_DATA,1)) start

CROSS APPLY (SELECT pos = 1+CHARINDEX('"@@T_301',assessment_data,start.pos)) [end]

WHERE ASSESSMENT_MONIKER = '09D27E579F9344A1BBD442E70CA1241F'

AND ASSESSMENT_DATA IS NOT NULL

AND start.pos > 0

AND [end].pos > start.pos

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jbalbo SSCertifiable Points: 7583 More actions · Answer 10

Thanks Chris,

That work great..

Now I have to do some reading so I know exactly what this is doing... lol

I really appreciate the time.....

Hope the weather is as nice in London as here in Boston....

Thanks Again

Joe

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Thanks for the feedback Joe. I'm in Hampshire for this gig, perhaps 50 miles west and south a bit from London, and it's about 30^oC. Absolutely awesome!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden