PARSING a Concatenated string into columns

Question

PARSING a Concatenated string into columns

SQL33

SSCrazy

Points: 2044
More actions
November 1, 2010 at 1:51 pm

#231071

A real puzzler for me.... I don't know how I'd create a test table for someone to play with from this.. but here's my question. The text below is the contents of a single concatenated field and a single record in a SQL2005 table. The field datatype is MSGText (varchar(1024),null) . I'd like to parse it on the fly in TSQL so each field is manually parsed as shown the second section where it is parsed with commas (by me) so I could apply criteria, etc to each of the resulting columns and they would be seen in the query results as seperate columns...
UNPARSED
//Nov 1 10:24:01 iprism: WEBhttp1288632241P10.6.40.32Staffcksduser\raenelll215http://www.google.com/csi?v=3&s=web&action=&ei=yvfOTN3lN5O-sQPj_eDQDg&e=17259,25638,26637,26667,27284,27357&cp=false&imp=0&pf=1&pfa=n.10,ttfc.324,ttlc.359,cbt.23&pfm=n.10,ttfc.734,ttlc.734,cbt.203&imn=1&rt=prt.94,pprt.109,ol.109,jsrt.328,iml.109web search0HTTPGET204text/html//
MANUALLY PARSED BY HAND WITH COMMAS
//Nov 1 10:24:01,iprism:, WEB,http,1288632241,P,10.6.40.32,Staff,cksduser\raenelll,215,http://www.google.com/csi?v=3&s=web&action=&ei=yvfOTN3lN5O-sQPj_eDQDg&e=17259,25638,26637,26667,27284,27357&cp=false&imp=0&pf=1&pfa=n.10,ttfc.324,ttlc.359,cbt.23&pfm=n.10,ttfc.734,ttlc.734,cbt.203&imn=1&rt=prt.94,pprt.109,ol.109,jsrt.328,iml.109,web search,0,HTTP,GET,204,text/html//

Viewing 14 posts - 1 through 13 (of 13 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

SQL33 (11/1/2010)
A real puzzler for me.... I don't know how I'd create a test table for someone to play with from this.. but here's my question. The text below is the contents of a single concatenated field and a single record in a SQL2005 table. The field datatype is MSGText (varchar(1024),null) . I'd like to parse it on the fly in TSQL so each field is manually parsed as shown the second section where it is parsed with commas (by me) so I could apply criteria, etc to each of the resulting columns and they would be seen in the query results as seperate columns...
UNPARSED
//Nov 1 10:24:01 iprism: WEBhttp1288632241P10.6.40.32Staffcksduser\raenelll215http://www.google.com/csi?v=3&s=web&action=&ei=yvfOTN3lN5O-sQPj_eDQDg&e=17259,25638,26637,26667,27284,27357&cp=false&imp=0&pf=1&pfa=n.10,ttfc.324,ttlc.359,cbt.23&pfm=n.10,ttfc.734,ttlc.734,cbt.203&imn=1&rt=prt.94,pprt.109,ol.109,jsrt.328,iml.109web search0HTTPGET204text/html//
MANUALLY PARSED BY HAND WITH COMMAS
//Nov 1 10:24:01,iprism:, WEB,http,1288632241,P,10.6.40.32,Staff,cksduser\raenelll,215,http://www.google.com/csi?v=3&s=web&action=&ei=yvfOTN3lN5O-sQPj_eDQDg&e=17259,25638,26637,26667,27284,27357&cp=false&imp=0&pf=1&pfa=n.10,ttfc.324,ttlc.359,cbt.23&pfm=n.10,ttfc.734,ttlc.734,cbt.203&imn=1&rt=prt.94,pprt.109,ol.109,jsrt.328,iml.109,web search,0,HTTP,GET,204,text/html//

Your best bet here would be to figure out the split (I'd like to see a few more rows before I could feel comfortable trying to create a splitter) into separate columns, then recombining the result with commas between the unique fields.

For example: SELECT LEFT(unparsed, CHARINDEX( ' ', unparsed)-1) AS firstfield, substring(unparsed, charindex(' ', unparsed) +1, 7) AS secondfield... etc

It won't be pretty, and I'd do it in a series of one field steps so that you could repair it on a 'per field' basis as you try to chew through your data.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

SQL33 (11/1/2010)
A real puzzler for me.... I don't know how I'd create a test table for someone to play with from this.. but here's my question. The text below is the contents of a single concatenated field and a single record in a SQL2005 table. The field datatype is MSGText (varchar(1024),null) . I'd like to parse it on the fly in TSQL so each field is manually parsed as shown the second section where it is parsed with commas (by me) so I could apply criteria, etc to each of the resulting columns and they would be seen in the query results as seperate columns...
UNPARSED
//Nov 1 10:24:01 iprism: WEBhttp1288632241P10.6.40.32Staffcksduser\raenelll215http://www.google.com/csi?v=3&s=web&action=&ei=yvfOTN3lN5O-sQPj_eDQDg&e=17259,25638,26637,26667,27284,27357&cp=false&imp=0&pf=1&pfa=n.10,ttfc.324,ttlc.359,cbt.23&pfm=n.10,ttfc.734,ttlc.734,cbt.203&imn=1&rt=prt.94,pprt.109,ol.109,jsrt.328,iml.109web search0HTTPGET204text/html//
MANUALLY PARSED BY HAND WITH COMMAS
//Nov 1 10:24:01,iprism:, WEB,http,1288632241,P,10.6.40.32,Staff,cksduser\raenelll,215,http://www.google.com/csi?v=3&s=web&action=&ei=yvfOTN3lN5O-sQPj_eDQDg&e=17259,25638,26637,26667,27284,27357&cp=false&imp=0&pf=1&pfa=n.10,ttfc.324,ttlc.359,cbt.23&pfm=n.10,ttfc.734,ttlc.734,cbt.203&imn=1&rt=prt.94,pprt.109,ol.109,jsrt.328,iml.109,web search,0,HTTP,GET,204,text/html//

It would appear that you've manually added commas to wherever there's a "white space" field of one or more spaces to parse on. Is that correct? We can do that but let me ask... do you have a target-table definition that you'd like to load this into?

Also, to test this, I need more than 1 row to make sure things are working correctly. Can you attach a small file of 10 or 20 rows I could load?

Last but not least, does each row in the unsplit table always start with "//" and does each row always end with "//"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

Ah... one more thing. It would also appear that this is trully "flat file" stuff with a fixed column length for each "field". Would that also be true?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL33 SSCrazy Points: 2044 More actions · Answer 4

The data is an event log output from a Web Filter device. I collect the log using a KIWI Syslog server which populates the collected log directly into a SQLTable. The result you see is from the resulting SQL table. Since the http part can change from row to row based on where the user browses it cannot be fixed width. The / and \ are put in by the syslog output... not by me.

Sample data attached...

Thanks in advance for your help and patience with this....

Jeff Moden (11/2/2010)
SQL33 (11/1/2010)
A real puzzler for me.... I don't know how I'd create a test table for someone to play with from this.. but here's my question. The text below is the contents of a single concatenated field and a single record in a SQL2005 table. The field datatype is MSGText (varchar(1024),null) . I'd like to parse it on the fly in TSQL so each field is manually parsed as shown the second section where it is parsed with commas (by me) so I could apply criteria, etc to each of the resulting columns and they would be seen in the query results as seperate columns...
UNPARSED
//Nov 1 10:24:01 iprism: WEBhttp1288632241P10.6.40.32Staffcksduser\raenelll215http://www.google.com/csi?v=3&s=web&action=&ei=yvfOTN3lN5O-sQPj_eDQDg&e=17259,25638,26637,26667,27284,27357&cp=false&imp=0&pf=1&pfa=n.10,ttfc.324,ttlc.359,cbt.23&pfm=n.10,ttfc.734,ttlc.734,cbt.203&imn=1&rt=prt.94,pprt.109,ol.109,jsrt.328,iml.109web search0HTTPGET204text/html//
MANUALLY PARSED BY HAND WITH COMMAS
//Nov 1 10:24:01,iprism:, WEB,http,1288632241,P,10.6.40.32,Staff,cksduser\raenelll,215,http://www.google.com/csi?v=3&s=web&action=&ei=yvfOTN3lN5O-sQPj_eDQDg&e=17259,25638,26637,26667,27284,27357&cp=false&imp=0&pf=1&pfa=n.10,ttfc.324,ttlc.359,cbt.23&pfm=n.10,ttfc.734,ttlc.734,cbt.203&imn=1&rt=prt.94,pprt.109,ol.109,jsrt.328,iml.109,web search,0,HTTP,GET,204,text/html//
It would appear that you've manually added commas to wherever there's a "white space" field of one or more spaces to parse on. Is that correct? We can do that but let me ask... do you have a target-table definition that you'd like to load this into?
Also, to test this, I need more than 1 row to make sure things are working correctly. Can you attach a small file of 10 or 20 rows I could load?
Last but not least, does each row in the unsplit table always start with "//" and does each row always end with "//"

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

The conversion to Excel messes up a bunch of things. Can you just direct the ouput of the query in the text message window to a file and attach the file, please? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL33 SSCrazy Points: 2044 More actions · Answer 6

The text file containing 50 records is attached.

Jeff Moden (11/2/2010)
The conversion to Excel messes up a bunch of things. Can you just direct the ouput of the query in the text message window to a file and attach the file, please? Thanks.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

Alright, end to end code. SQL33, please note, I have no idea what a number of these fields mean, and if I got your spacing off, there should be enough different methods in here as a sample to find what you're looking for. I got a little generic near the end because of time, but there's a few diff methods at the beginning. Also, near the end, pay attention to how I reversed the data because of the one field in here that's completely variable. You basically have to end up isolating it from both directions.

dROP TABLE #tmp

CREATE TABLE #tmp ( data VARCHAR(8000))

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:21:21Local7.Debughost.company.comThis is a test message from Kiwi Syslog Server')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.6.40.32 Staff cksduser\raenelll 215 http://www.google.com/csi?v=3&s=web&action=&ei=yvfOTN3lN5O-sQPj_eDQDg&e=17259,25638,26637,26667,27284,27357&cp=false&imp=0&pf=1&pfa=n.10,ttfc.324,ttlc.359,cbt.23&pfm=n.10,ttfc.734,ttlc.734,cbt.203&imn=1&rt=prt.94,pprt.109,ol.109,jsrt.328,iml.109 web search 0 HTTPGET 204 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.6.40.32 Staff cksduser\raenelll 103086 http://www.google.com/search?sclient=psy&hl=en&q=bruising+easily+causes&aq=f&aqi=g4g-o1&aql=&oq=&gs_rfai=&pbx=1&emsg=NCSR&noj=1&ei=yvfOTN3lN5O-sQPj_eDQDg web search 0 HTTPGET 204 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.6.40.32 Staff cksduser\raenelll 560 http://www.google.com/search?sclient=psy&hl=en&q=bruising+easily+causes&aq=f&aqi=g4g-o1&aql=&oq=&gs_rfai=&pbx=1&tch=3&ech=1&psi=v_fOTIDrKo-csQOpr8TsBw12886322586090&wrapid=tlif12886322586091&safe=strict web search 0 HTTPGET 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.31.42.6 CKSD_Students cksduser\day1011h 1029 http://hb.pro-market.net/engine?site=123873;size=1x1;category=Music;kw=Are%20there%20any%20famous%20deaf%20musicians;siteref=http%3A//www.google.com/search%3Fq%3Ddeaf+musicians%26hl%3Den%26safe%3Dstrict%26noj%3D1%26ei%3DnvfOTKStEoLQsAP9h6GGDw%26start%3D10%26sa%3DN professional services 0 HTTPGET 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 13690 http://ads.pubmatic.com/AdServer/js/showad.js professional services 0 HTTPGET 200 application/x-javascript')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.31.42.6 CKSD_Students cksduser\day1011h 576 http://www.google.com/recaptcha/api//img/clean/audio.png web search 0 HTTPGET 200 image/png')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.23.31.143 Staff cksduser\ilonar 2403 http://l1.yimg.com/a/i/ww/news/2010/10/30/rihanna-pdsm.jpg internet services 0 HTTPGET 200 image/jpeg')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 167 http://col.stc.s-msn.com/br/sc/css/c3/0ca4b4acd88ee0d1e353579291a8b1.css internet services 0 HTTPGET 304 text/css')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.23.31.143 Staff cksduser\ilonar 4135 http://l1.yimg.com/a/i/ww/news/2010/10/28/nursewithfiles-pdsm.jpg internet services 0 HTTPGET 200 image/jpeg')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.23.31.143 Staff cksduser\ilonar 239 http://www.yahoo.com/p.gif;_ylt=At0LZyK0_QWnqvczQnnR96ibvZx4?t=1288632272936 web search 0 HTTPGET 204 text/plain')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.23.31.143 Staff cksduser\ilonar 6131 http://l1.yimg.com/a/i/ww/news/2010/10/29/bondwithher-sm.jpg internet services 0 HTTPGET 200 image/jpeg')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 316 http://udc.msn.com/c.gif?di=340&pi=7317&ps=95101&br=MSFT&mk=en-us&pn=US+HPMSFT3W&pid=6713487&mv=V14&su=http%3A%2F%2Fwww.msn.com%2Fdefaultwpe3w.aspx&fk=D1&gp=P&optkey=optkey7&clid=88F1B61EC267415DAFBAD49CE4EFCF20&cu=http%3A%2F%2Fwww.msn.com%2F&sl=1&slv=4.0&bh=751&bw=1260&scr=1280x960&sd=32&cts=1288632270828&dv.SNLogin=fb%3Af%2Ctw%3Af&dv.GrpFrMod=infopane_hops%3Ana%2Cmaintg%3Alatest_hops%2Csectabs%3Aentertainment%2Clocaltg%3Alocal%2Cstgsearch%3Apopsrch%2Csocialtg%3Afacebook%2Cgendermodule%3Aforher&hp=N&rid=f4e9c9c4106c410cb297b3d333f95476&pp=False&evt=impr&js=1 web search 0 HTTPGET 200 image/gif')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 256 http://view.atdmt.com/action/MSN_Homepage_Remessaging_111808/nc?a=1 web banners 0 HTTPGET 200 image/gif')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 545 http://ads.pubmatic.com/AdServer/js/freq.html professional services 0 HTTPGET 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 2615 http://ads.pubmatic.com/AdServer/js/syncuppixels.html professional services 0 HTTPGET 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 170 http://col.stb.s-msn.com/i/BA/F7AFD6FD9371ACDFE1873AA174F5E.png internet services 0 HTTPGET 304 image/png')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 167 http://col.stc.s-msn.com/br/sc/css/13/e5607048024c9fd4df412c394d1c21.css internet services 0 HTTPGET 304 text/css')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.2.30.63 CKSD_Students cksduser\leroy7849j 336 http://lms.arcademicskillbuilders.com/api/load_top_scores?game_name=demolition&jsoncallback=jsonp1288632212988 k12 0 HTTPGET 200 application/x-javascript')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.22.29.1 Staff cksduser\craigh 767 http://clients1.google.com/complete/search?hl=en&client=hp&expIds=17259,17315,18168,23628,23670,25638,25983,26328,26637,26761,26849,26869,27126,27284,27357&sugexp=ldymls&xhr=t&q=lenore%20l&cp=7&tch=4&ech=4&psi=zPfOTP3GIoPIowSI4ZnHDw12886322726110&wrapid=tljp128863227261103 web search 0 HTTPGET 200 application/json')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 170 http://col.stc.s-msn.com/br/sc/i/FF/FA134E11C3EB51172B8C565507FAC1.png internet services 0 HTTPGET 304 image/png')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.4.30.141 Staff cksduser\joem 48301 http://www.shopsbt.com/jet-skis/jet-ski-engines-crankshafts.html automotive 0 HTTPGET 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 528 http://bpx.a9.com/ads/render?p=634&t=1050&r=717096 internet services 0 HTTPGET 200 text/javascript')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 8826 http://col.stb.s-msn.com/i/AB/4EA2A262C81687AABB72EC41F1FA4B.jpg internet services 0 HTTPGET 200 image/jpeg')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.7.40.40 CKSD_Students cksduser\dyers0593c 767 http://xtramath.org/student/instructions/36382 k12 0 HTTPPOST 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.42.6 CKSD_Students cksduser\day1011h 812 http://ads.pro-market.net/ads/scripts/site-123873.js web banners 0 HTTPGET 200 application/x-javascript')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632241 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 1882 http://showads.pubmatic.com/AdServer/AdServerServlet?operId=2&pubId=25273&siteId=25281&adId=19972&kadwidth=728&kadheight=90&kbgColor=ffffff&ktextColor=000000&klinkColor=0000ff&pageURL=http://bpx.a9.com/amzn/iframe.html&frameName=http_bpx_a9_comamzniframe_htmlkomli_ads_frame12527325281&kltstamp=2010-10-1%2010%3A24%3A33&ranreq=0.2042963749833443&timezone=-7&screenResolution=1680x1050&inIframe=1&adPosition=-1x-1&adVisibility=0 professional services 0 HTTPGET 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 373 http://track.pubmatic.com/AdServer/AdDisplayTrackerServlet?operId=1&pubId=25273&siteId=25281&adId=19972&adServerId=661&kefact=1.198980&kpbmtpfact=0.000000&kadNetFrequecy=1&kadwidth=728&kadheight=90&kltstamp=1288632270&indirectAdId=24815&adServerOptimizerId=1&ranreq=0.2042963749833443&imprCap=1&pageURL=http://bpx.a9.com/amzn/iframe.html professional services 0 HTTPGET 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.41.179 Staff cksduser\davidt 394 http://cdn.eyewonder.com/100125/764455/1376086/dot.gif?ewadid=122477&ewbust=1288632236963&ad=1376086&vis=true&percent=96&visChg=true&vistime=10&ttlvistime=29&interactions=0&timeOnPage=30&yscroll=0&xscroll=0&yPos=434&xPos=824&res=1327x676&bsizeChg=false&guid=grt92WCtxRHg$TdSGN6Y2u internet services 0 HTTPGET 200 image/gif')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 474 http://pixel.quantserve.com/pixel/p-5aWVS_roA1dVM.gif?labels=Entertainment_and_Leisure internet services 0 HTTPGET 200 image/gif')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.11.30.16 CKSD_Students cksduser\lilly0750k 8252 http://www.google.com/search?hl=en&safe=strict&rls=com.microsoft:en-us&&sa=X&ei=y_fOTJuKI5CusAPF9oXdDg&ved=0CB0QBSgA&q=softschools.com/games&spell=1&fp=8e8f35a77978fbf6&tch=3&ech=1&psi=y_fOTJuKI5CusAPF9oXdDg12886323046460&wrapid=tlif12886323046461 web search 0 HTTPGET 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 586 http://image2.pubmatic.com/AdServer/Pug?vcode=bz0xJnR5cGU9MSZqcz0xJmNvZGU9NzkmdGw9MTQ0MCZkcF9pZD01Nw==&vcode=bz0yJnR5cGU9MSZqcz0xJmNvZGU9NzgmdGw9MTU3NjgwMCZkcF9pZD01Nw==&piggybackCookie=uid:0 professional services 0 HTTPGET 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.32.41.147 CKSD_Students cksduser\burrell7852k 146 http://96.17.69.132/idle/q-Fmdz02ySLmWnFV/84 other sites 0 HTTPPOST 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 598 http://pixel.invitemedia.com/pubmatic_sync?pubmatic_callback=http://image2.pubmatic.com/AdServer/Pug?vcode=bz0yJnR5cGU9MSZjb2RlPTM5MCZ0bD0xMjk2MDA=&piggybackCookie= professional services 0 HTTPGET 302 text/plain')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 1200 http://r1.ace.advertising.com/site=789981/size=728090/u=2/bnum=6336858/hr=10/hl=3/c=2/scres=5/swh=1680x1050/tile=1/f=2/r=1/optn=1/fv=10/aolexp=0/dref=http%253A%252F%252Fwww.imdb.com%252Fimages%252FSFbf9160854df5dfed1fbb12a1ba981db9%252Fa%252Fifb%252Fdoubleclick%252Fexpand.html professional services 0 HTTPGET 302 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 B 10.31.41.85 CKSD_Students cksduser\talbert2363c 0 http://r.openx.net/set?pid=21a19823-5de3-4917-bc81-a4edea5127ff&rtb=7292260776583750807 corporate marketing, Web Log(Blog) 0 HTTPGET 0 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 14697 http://col.stb.s-msn.com/i/B0/D9F595C8B26AA2862353255D8F8A2.jpg internet services 0 HTTPGET 200 image/jpeg')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 6538 http://col.stb.s-msn.com/i/86/CE11B988D27CC6B3A7E63DACC1E8.jpg internet services 0 HTTPGET 200 image/jpeg')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 170 http://col.stb.s-msn.com/i/E2/37BA92E210D341BFDBF4126422A3D2.gif internet services 0 HTTPGET 304 image/gif')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.32.41.109 Staff cksduser\andrewd 202 http://odu.edu/fusion/audiences/students.inc continuing education/colleges 0 HTTPGET 304 unknown/unknown')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 170 http://col.stc.s-msn.com/br/sc/i/94/8b0fe9bcd1399077fdc9374e5f314d.png internet services 0 HTTPGET 304 image/png')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 1347 http://bpx.a9.com/amzn/iframe.html?p=635&t=1052&r=984668 internet services 0 HTTPGET 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 1979 http://col.stb.s-msn.com/i/8D/7F8CA3E0D297F046F6AD8F981BEB.gif internet services 0 HTTPGET 200 image/gif')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 624 http://image2.pubmatic.com/AdServer/Pug?vcode=bz0yJnR5cGU9MSZqcz0xJmNvZGU9ODImdGw9MTU3NjgwMCZkcF9pZD0yMg==&piggybackCookie=pcv:1|uid:7292260776583750807 professional services 0 HTTPGET 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.22.29.1 Staff cksduser\craigh 765 http://clients1.google.com/complete/search?hl=en&client=hp&expIds=17259,17315,18168,23628,23670,25638,25983,26328,26637,26761,26849,26869,27126,27284,27357&sugexp=ldymls&xhr=t&q=lenore%20lak&cp=9&tch=4&ech=5&psi=zPfOTP3GIoPIowSI4ZnHDw12886322726110&wrapid=tljp128863227261104 web search 0 HTTPGET 200 application/json')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 724 http://image2.pubmatic.com/AdServer/Pug?vcode=bz0yJnR5cGU9MSZjb2RlPTM5MCZ0bD0xMjk2MDA%3D&piggybackCookie=c4c38bf9-b6a8-496d-9c87-d8943e3fb718. professional services 0 HTTPGET 200 image/gif')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 824 http://ads.adbrite.com/adserver/vdi/742697?d=7292260776583750807 web banners 0 HTTPGET 200 image/gif')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.42.6 CKSD_Students cksduser\day1011h 32989 http://www.chacha.com/question/are-there-any-famous-deaf-musicians web search 0 HTTPGET 200 text/html')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 169 http://col.stb.s-msn.com/i/FE/227C9B7B91A3F2533701264ADCEE.jpg internet services 0 HTTPGET 304 image/jpeg')

INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 1632 http://r1.ace.advertising.com/ctst=1/site=789981/size=728090/u=2/bnum=6336858/hr=10/hl=3/c=2/scres=5/swh=1680x1050/tile=1/f=2/r=1/optn=1/fv=10/aolexp=0/dref=http%253A%252F%252Fwww.imdb.com%252Fimages%252FSFbf9160854df5dfed1fbb12a1ba981db9%252Fa%252Fifb%252Fdoubleclick%252Fexpand.html professional services 0 HTTPGET 200 application/x-javascript')

INSERT INTO #tmp (data) VALUES ( 'NULLNULLNULLNULLNULL')

select * from #tmp

DELETE FROM #tmp WHERE LEFT( data, 4) = 'NULL'

DELETE FROM #tmp WHERE data like '%This is a test message%'

ALTER TABLE #tmp ADD FirstDatetime DATETIME

ALTER TABLE #tmp ADD LocalAddress VARCHAR(50)

ALTER TABLE #tmp ADD IPAddress VARCHAR(30)

ALTER TABLE #tmp ADD DT2 DATETIME

ALTER TABLE #tmp ADD iprismSource VARCHAR(50)

ALTER TABLE #tmp ADD IP2 VARCHAR(15)

ALTER TABLE #tmp ADD Staff_student VARCHAR(50)

ALTER TABLE #tmp ADD LoginName VARCHAR(50)

ALTER TABLE #tmp ADD SomeInt INT

ALTER TABLE #tmp ADD HTTPAddr VARCHAR(1000)

ALTER TABLE #tmp ADD Source VARCHAR(100)

ALTER TABLE #tmp ADD SomeInt2 INT

ALTER TABLE #tmp ADD HTTPTypeVARCHAR(20)

ALTER TABLE #tmp ADD SomeInt3 INT

ALTER TABLE #tmp ADD pageType VARCHAR(50)

SELECT ASCII( ' '), ASCII( substring( data, 11, 1)) FROM #tmp

UPDATE #tmp

--Char9 is a tab. Swap to Space

SETFirstDateTime = CAST( REPLACE( LEFT( data, 19), CHAR(9), CHAR(32) ) AS DATETIME),

data = RIGHT( data, len( data )- 20 )

UPDATE #tmp

SET

LocalAddress = LEFT( data, CHARINDEX( CHAR(9), data) -1),

data = RIGHT( data, len(data) - charindex( CHAR(9), data))

UPDATE #tmp

SET

IPAddress = LEFT( data, CHARINDEX( CHAR(9), data) -1),

data = RIGHT( data, len(data) - charindex( CHAR(9), data))

-- We've run out of tabs, time to go to spaces.

UPDATE #tmp

SET DT2 = CAST(

STUFF(

REPLACE( CHAR(32) + CHAR(32), CHAR(32), --remove double spaces

LEFT( data, CHARINDEX( CHAR(32), data,

CHARINDEX( CHAR(32), data, 7) +1)) -- Skip to 7 for the JAN 1 structure, then find the tail.

) -- End Replace

, 7, 0, ', ' + CAST( DATEPART( yyyy, GETDATE()) AS VARCHAR(8))

) -- End Stuff

AS DATETIME),

data = right( data, len(data) -

LEN( REPLACE( CHAR(32) + CHAR(32), CHAR(32), --remove double spaces

LEFT( data, CHARINDEX( CHAR(32), data,

CHARINDEX( CHAR(32), data, 7) +1)) -- Skip to 7 for the JAN 1 structure, then find the tail.

) -- End Replace

) -1)

UPDATE #tmp

SETiprismsource = LEFT( data, 28),

data = RIGHT( data, len(data) - 30)

UPDATE #tmp

SET

IP2 = LEFT( data, CHARINDEX( ' ', data)-1),

data = RIGHT( data, len(data) - CHARINDEX( ' ', data))

FROM #tmp

UPDATE #tmp

SET

Staff_student = LEFT( data, CHARINDEX( ' ', data)-1),

data = RIGHT( data, len(data) - CHARINDEX( ' ', data))

FROM #tmp

UPDATE #tmp

SET

LoginName = LEFT( data, CHARINDEX( ' ', data)-1),

data = RIGHT( data, len(data) - CHARINDEX( ' ', data))

FROM #tmp

UPDATE #tmp

SET

SomeInt = CAST( LEFT( data, CHARINDEX( ' ', data)-1) AS INT),

data = RIGHT( data, len(data) - CHARINDEX( ' ', data))

FROM #tmp

UPDATE #tmp

SET

HTTPAddr = LEFT( data, CHARINDEX( ' ', data)-1),

data = RIGHT( data, len(data) - CHARINDEX( ' ', data))

FROM #tmp

-- We're going to reverse here, because corporate marketing, weblog(blog)

-- throws off the pattern here, so we'll work in from the back.

UPDATE #tmp SET data = REVERSE( data)

UPDATE #tmp

SET

pagetype = REVERSE( LEFT( data, CHARINDEX( ' ', data)-1)),

data = RIGHT( data, len(data) - CHARINDEX( ' ', data))

FROM #tmp

UPDATE #tmp

SET

SomeInt3 = CAST( REVERSE( LEFT( data, CHARINDEX( ' ', data)-1)) AS INT),

data = RIGHT( data, len(data) - CHARINDEX( ' ', data))

FROM #tmp

UPDATE #tmp

SET

HTTPType = REVERSE( LEFT( data, CHARINDEX( ' ', data)-1)),

data = RIGHT( data, len(data) - CHARINDEX( ' ', data))

FROM #tmp

UPDATE #tmp

SET

SomeInt2 = CAST( REVERSE( LEFT( data, CHARINDEX( ' ', data)-1)) AS INT),

data = RIGHT( data, len(data) - CHARINDEX( ' ', data))

FROM #tmp

UPDATE #tmp

SETSource = REVERSE( data),

data = ''

SELECT * FROM #tmp

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

SQL33 SSCrazy Points: 2044 More actions · Answer 8

It looks like this code combines the results of the first few columns and then parses them along with the last column (the MsgText http column) back out. I'm not entirely clear on why you needed to do that... and, I'm getting errors when I run it as a single query. Can you provide a little more explanation on what's going on with the various parts?

Thanks..

Craig Farrell (11/2/2010)
Alright, end to end code. SQL33, please note, I have no idea what a number of these fields mean, and if I got your spacing off, there should be enough different methods in here as a sample to find what you're looking for. I got a little generic near the end because of time, but there's a few diff methods at the beginning. Also, near the end, pay attention to how I reversed the data because of the one field in here that's completely variable. You basically have to end up isolating it from both directions.
dROP TABLE #tmp
CREATE TABLE #tmp ( data VARCHAR(8000))
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:21:21Local7.Debughost.company.comThis is a test message from Kiwi Syslog Server')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.6.40.32 Staff cksduser\raenelll 215 http://www.google.com/csi?v=3&s=web&action=&ei=yvfOTN3lN5O-sQPj_eDQDg&e=17259,25638,26637,26667,27284,27357&cp=false&imp=0&pf=1&pfa=n.10,ttfc.324,ttlc.359,cbt.23&pfm=n.10,ttfc.734,ttlc.734,cbt.203&imn=1&rt=prt.94,pprt.109,ol.109,jsrt.328,iml.109 web search 0 HTTPGET 204 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.6.40.32 Staff cksduser\raenelll 103086 http://www.google.com/search?sclient=psy&hl=en&q=bruising+easily+causes&aq=f&aqi=g4g-o1&aql=&oq=&gs_rfai=&pbx=1&emsg=NCSR&noj=1&ei=yvfOTN3lN5O-sQPj_eDQDg web search 0 HTTPGET 204 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.6.40.32 Staff cksduser\raenelll 560 http://www.google.com/search?sclient=psy&hl=en&q=bruising+easily+causes&aq=f&aqi=g4g-o1&aql=&oq=&gs_rfai=&pbx=1&tch=3&ech=1&psi=v_fOTIDrKo-csQOpr8TsBw12886322586090&wrapid=tlif12886322586091&safe=strict web search 0 HTTPGET 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.31.42.6 CKSD_Students cksduser\day1011h 1029 http://hb.pro-market.net/engine?site=123873;size=1x1;category=Music;kw=Are%20there%20any%20famous%20deaf%20musicians;siteref=http%3A//www.google.com/search%3Fq%3Ddeaf+musicians%26hl%3Den%26safe%3Dstrict%26noj%3D1%26ei%3DnvfOTKStEoLQsAP9h6GGDw%26start%3D10%26sa%3DN professional services 0 HTTPGET 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 13690 http://ads.pubmatic.com/AdServer/js/showad.js professional services 0 HTTPGET 200 application/x-javascript')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.31.42.6 CKSD_Students cksduser\day1011h 576 http://www.google.com/recaptcha/api//img/clean/audio.png web search 0 HTTPGET 200 image/png')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.23.31.143 Staff cksduser\ilonar 2403 http://l1.yimg.com/a/i/ww/news/2010/10/30/rihanna-pdsm.jpg internet services 0 HTTPGET 200 image/jpeg')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 167 http://col.stc.s-msn.com/br/sc/css/c3/0ca4b4acd88ee0d1e353579291a8b1.css internet services 0 HTTPGET 304 text/css')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.23.31.143 Staff cksduser\ilonar 4135 http://l1.yimg.com/a/i/ww/news/2010/10/28/nursewithfiles-pdsm.jpg internet services 0 HTTPGET 200 image/jpeg')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.23.31.143 Staff cksduser\ilonar 239 http://www.yahoo.com/p.gif;_ylt=At0LZyK0_QWnqvczQnnR96ibvZx4?t=1288632272936 web search 0 HTTPGET 204 text/plain')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.23.31.143 Staff cksduser\ilonar 6131 http://l1.yimg.com/a/i/ww/news/2010/10/29/bondwithher-sm.jpg internet services 0 HTTPGET 200 image/jpeg')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 316 http://udc.msn.com/c.gif?di=340&pi=7317&ps=95101&br=MSFT&mk=en-us&pn=US+HPMSFT3W&pid=6713487&mv=V14&su=http%3A%2F%2Fwww.msn.com%2Fdefaultwpe3w.aspx&fk=D1&gp=P&optkey=optkey7&clid=88F1B61EC267415DAFBAD49CE4EFCF20&cu=http%3A%2F%2Fwww.msn.com%2F&sl=1&slv=4.0&bh=751&bw=1260&scr=1280x960&sd=32&cts=1288632270828&dv.SNLogin=fb%3Af%2Ctw%3Af&dv.GrpFrMod=infopane_hops%3Ana%2Cmaintg%3Alatest_hops%2Csectabs%3Aentertainment%2Clocaltg%3Alocal%2Cstgsearch%3Apopsrch%2Csocialtg%3Afacebook%2Cgendermodule%3Aforher&hp=N&rid=f4e9c9c4106c410cb297b3d333f95476&pp=False&evt=impr&js=1 web search 0 HTTPGET 200 image/gif')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 256 http://view.atdmt.com/action/MSN_Homepage_Remessaging_111808/nc?a=1 web banners 0 HTTPGET 200 image/gif')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 545 http://ads.pubmatic.com/AdServer/js/freq.html professional services 0 HTTPGET 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 2615 http://ads.pubmatic.com/AdServer/js/syncuppixels.html professional services 0 HTTPGET 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 170 http://col.stb.s-msn.com/i/BA/F7AFD6FD9371ACDFE1873AA174F5E.png internet services 0 HTTPGET 304 image/png')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 167 http://col.stc.s-msn.com/br/sc/css/13/e5607048024c9fd4df412c394d1c21.css internet services 0 HTTPGET 304 text/css')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 167 http://col.stc.s-msn.com/br/sc/css/13/e5607048024c9fd4df412c394d1c21.css internet services 0 HTTPGET 304 text/css')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.2.30.63 CKSD_Students cksduser\leroy7849j 336 http://lms.arcademicskillbuilders.com/api/load_top_scores?game_name=demolition&jsoncallback=jsonp1288632212988 k12 0 HTTPGET 200 application/x-javascript')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.22.29.1 Staff cksduser\craigh 767 http://clients1.google.com/complete/search?hl=en&client=hp&expIds=17259,17315,18168,23628,23670,25638,25983,26328,26637,26761,26849,26869,27126,27284,27357&sugexp=ldymls&xhr=t&q=lenore%20l&cp=7&tch=4&ech=4&psi=zPfOTP3GIoPIowSI4ZnHDw12886322726110&wrapid=tljp128863227261103 web search 0 HTTPGET 200 application/json')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.30.29.2 Staff cksduser\timc 170 http://col.stc.s-msn.com/br/sc/i/FF/FA134E11C3EB51172B8C565507FAC1.png internet services 0 HTTPGET 304 image/png')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.4.30.141 Staff cksduser\joem 48301 http://www.shopsbt.com/jet-skis/jet-ski-engines-crankshafts.html automotive 0 HTTPGET 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:01 iprism: WEB http 1288632241 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 528 http://bpx.a9.com/ads/render?p=634&t=1050&r=717096 internet services 0 HTTPGET 200 text/javascript')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 8826 http://col.stb.s-msn.com/i/AB/4EA2A262C81687AABB72EC41F1FA4B.jpg internet services 0 HTTPGET 200 image/jpeg')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.7.40.40 CKSD_Students cksduser\dyers0593c 767 http://xtramath.org/student/instructions/36382 k12 0 HTTPPOST 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.42.6 CKSD_Students cksduser\day1011h 812 http://ads.pro-market.net/ads/scripts/site-123873.js web banners 0 HTTPGET 200 application/x-javascript')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632241 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 1882 http://showads.pubmatic.com/AdServer/AdServerServlet?operId=2&pubId=25273&siteId=25281&adId=19972&kadwidth=728&kadheight=90&kbgColor=ffffff&ktextColor=000000&klinkColor=0000ff&pageURL=http://bpx.a9.com/amzn/iframe.html&frameName=http_bpx_a9_comamzniframe_htmlkomli_ads_frame12527325281&kltstamp=2010-10-1%2010%3A24%3A33&ranreq=0.2042963749833443&timezone=-7&screenResolution=1680x1050&inIframe=1&adPosition=-1x-1&adVisibility=0 professional services 0 HTTPGET 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 373 http://track.pubmatic.com/AdServer/AdDisplayTrackerServlet?operId=1&pubId=25273&siteId=25281&adId=19972&adServerId=661&kefact=1.198980&kpbmtpfact=0.000000&kadNetFrequecy=1&kadwidth=728&kadheight=90&kltstamp=1288632270&indirectAdId=24815&adServerOptimizerId=1&ranreq=0.2042963749833443&imprCap=1&pageURL=http://bpx.a9.com/amzn/iframe.html professional services 0 HTTPGET 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.41.179 Staff cksduser\davidt 394 http://cdn.eyewonder.com/100125/764455/1376086/dot.gif?ewadid=122477&ewbust=1288632236963&ad=1376086&vis=true&percent=96&visChg=true&vistime=10&ttlvistime=29&interactions=0&timeOnPage=30&yscroll=0&xscroll=0&yPos=434&xPos=824&res=1327x676&bsizeChg=false&guid=grt92WCtxRHg$TdSGN6Y2u internet services 0 HTTPGET 200 image/gif')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 474 http://pixel.quantserve.com/pixel/p-5aWVS_roA1dVM.gif?labels=Entertainment_and_Leisure internet services 0 HTTPGET 200 image/gif')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.11.30.16 CKSD_Students cksduser\lilly0750k 8252 http://www.google.com/search?hl=en&safe=strict&rls=com.microsoft:en-us&&sa=X&ei=y_fOTJuKI5CusAPF9oXdDg&ved=0CB0QBSgA&q=softschools.com/games&spell=1&fp=8e8f35a77978fbf6&tch=3&ech=1&psi=y_fOTJuKI5CusAPF9oXdDg12886323046460&wrapid=tlif12886323046461 web search 0 HTTPGET 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 586 http://image2.pubmatic.com/AdServer/Pug?vcode=bz0xJnR5cGU9MSZqcz0xJmNvZGU9NzkmdGw9MTQ0MCZkcF9pZD01Nw==&vcode=bz0yJnR5cGU9MSZqcz0xJmNvZGU9NzgmdGw9MTU3NjgwMCZkcF9pZD01Nw==&piggybackCookie=uid:0 professional services 0 HTTPGET 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.32.41.147 CKSD_Students cksduser\burrell7852k 146 http://96.17.69.132/idle/q-Fmdz02ySLmWnFV/84 other sites 0 HTTPPOST 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 598 http://pixel.invitemedia.com/pubmatic_sync?pubmatic_callback=http://image2.pubmatic.com/AdServer/Pug?vcode=bz0yJnR5cGU9MSZjb2RlPTM5MCZ0bD0xMjk2MDA=&piggybackCookie= professional services 0 HTTPGET 302 text/plain')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 1200 http://r1.ace.advertising.com/site=789981/size=728090/u=2/bnum=6336858/hr=10/hl=3/c=2/scres=5/swh=1680x1050/tile=1/f=2/r=1/optn=1/fv=10/aolexp=0/dref=http%253A%252F%252Fwww.imdb.com%252Fimages%252FSFbf9160854df5dfed1fbb12a1ba981db9%252Fa%252Fifb%252Fdoubleclick%252Fexpand.html professional services 0 HTTPGET 302 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 B 10.31.41.85 CKSD_Students cksduser\talbert2363c 0 http://r.openx.net/set?pid=21a19823-5de3-4917-bc81-a4edea5127ff&rtb=7292260776583750807 corporate marketing, Web Log(Blog) 0 HTTPGET 0 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 14697 http://col.stb.s-msn.com/i/B0/D9F595C8B26AA2862353255D8F8A2.jpg internet services 0 HTTPGET 200 image/jpeg')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 6538 http://col.stb.s-msn.com/i/86/CE11B988D27CC6B3A7E63DACC1E8.jpg internet services 0 HTTPGET 200 image/jpeg')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 170 http://col.stb.s-msn.com/i/E2/37BA92E210D341BFDBF4126422A3D2.gif internet services 0 HTTPGET 304 image/gif')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.32.41.109 Staff cksduser\andrewd 202 http://odu.edu/fusion/audiences/students.inc continuing education/colleges 0 HTTPGET 304 unknown/unknown')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 170 http://col.stc.s-msn.com/br/sc/i/94/8b0fe9bcd1399077fdc9374e5f314d.png internet services 0 HTTPGET 304 image/png')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 1347 http://bpx.a9.com/amzn/iframe.html?p=635&t=1052&r=984668 internet services 0 HTTPGET 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 1979 http://col.stb.s-msn.com/i/8D/7F8CA3E0D297F046F6AD8F981BEB.gif internet services 0 HTTPGET 200 image/gif')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 624 http://image2.pubmatic.com/AdServer/Pug?vcode=bz0yJnR5cGU9MSZqcz0xJmNvZGU9ODImdGw9MTU3NjgwMCZkcF9pZD0yMg==&piggybackCookie=pcv:1|uid:7292260776583750807 professional services 0 HTTPGET 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.22.29.1 Staff cksduser\craigh 765 http://clients1.google.com/complete/search?hl=en&client=hp&expIds=17259,17315,18168,23628,23670,25638,25983,26328,26637,26761,26849,26869,27126,27284,27357&sugexp=ldymls&xhr=t&q=lenore%20lak&cp=9&tch=4&ech=5&psi=zPfOTP3GIoPIowSI4ZnHDw12886322726110&wrapid=tljp128863227261104 web search 0 HTTPGET 200 application/json')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 724 http://image2.pubmatic.com/AdServer/Pug?vcode=bz0yJnR5cGU9MSZjb2RlPTM5MCZ0bD0xMjk2MDA%3D&piggybackCookie=c4c38bf9-b6a8-496d-9c87-d8943e3fb718. professional services 0 HTTPGET 200 image/gif')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 824 http://ads.adbrite.com/adserver/vdi/742697?d=7292260776583750807 web banners 0 HTTPGET 200 image/gif')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.42.6 CKSD_Students cksduser\day1011h 32989 http://www.chacha.com/question/are-there-any-famous-deaf-musicians web search 0 HTTPGET 200 text/html')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.30.29.2 Staff cksduser\timc 169 http://col.stb.s-msn.com/i/FE/227C9B7B91A3F2533701264ADCEE.jpg internet services 0 HTTPGET 304 image/jpeg')
INSERT INTO #tmp (data) VALUES ( '2010-11-0110:24:33Local4.Info10.0.1.11Nov 1 10:24:02 iprism: WEB http 1288632242 P 10.31.41.85 CKSD_Students cksduser\talbert2363c 1632 http://r1.ace.advertising.com/ctst=1/site=789981/size=728090/u=2/bnum=6336858/hr=10/hl=3/c=2/scres=5/swh=1680x1050/tile=1/f=2/r=1/optn=1/fv=10/aolexp=0/dref=http%253A%252F%252Fwww.imdb.com%252Fimages%252FSFbf9160854df5dfed1fbb12a1ba981db9%252Fa%252Fifb%252Fdoubleclick%252Fexpand.html professional services 0 HTTPGET 200 application/x-javascript')
INSERT INTO #tmp (data) VALUES ( 'NULLNULLNULLNULLNULL')
select * from #tmp
DELETE FROM #tmp WHERE LEFT( data, 4) = 'NULL'
DELETE FROM #tmp WHERE data like '%This is a test message%'
ALTER TABLE #tmp ADD FirstDatetime DATETIME
ALTER TABLE #tmp ADD LocalAddress VARCHAR(50)
ALTER TABLE #tmp ADD IPAddress VARCHAR(30)
ALTER TABLE #tmp ADD DT2 DATETIME
ALTER TABLE #tmp ADD iprismSource VARCHAR(50)
ALTER TABLE #tmp ADD IP2 VARCHAR(15)
ALTER TABLE #tmp ADD Staff_student VARCHAR(50)
ALTER TABLE #tmp ADD LoginName VARCHAR(50)
ALTER TABLE #tmp ADD SomeInt INT
ALTER TABLE #tmp ADD HTTPAddr VARCHAR(1000)
ALTER TABLE #tmp ADD Source VARCHAR(100)
ALTER TABLE #tmp ADD SomeInt2 INT
ALTER TABLE #tmp ADD HTTPTypeVARCHAR(20)
ALTER TABLE #tmp ADD SomeInt3 INT
ALTER TABLE #tmp ADD pageType VARCHAR(50)
SELECT ASCII( ' '), ASCII( substring( data, 11, 1)) FROM #tmp
UPDATE #tmp
--Char9 is a tab. Swap to Space
SETFirstDateTime = CAST( REPLACE( LEFT( data, 19), CHAR(9), CHAR(32) ) AS DATETIME),
data = RIGHT( data, len( data )- 20 )
UPDATE #tmp
SET
LocalAddress = LEFT( data, CHARINDEX( CHAR(9), data) -1),
data = RIGHT( data, len(data) - charindex( CHAR(9), data))
UPDATE #tmp
SET
IPAddress = LEFT( data, CHARINDEX( CHAR(9), data) -1),
data = RIGHT( data, len(data) - charindex( CHAR(9), data))
-- We've run out of tabs, time to go to spaces.
UPDATE #tmp
SET DT2 = CAST(
STUFF(
REPLACE( CHAR(32) + CHAR(32), CHAR(32), --remove double spaces
LEFT( data, CHARINDEX( CHAR(32), data,
CHARINDEX( CHAR(32), data, 7) +1)) -- Skip to 7 for the JAN 1 structure, then find the tail.
) -- End Replace
, 7, 0, ', ' + CAST( DATEPART( yyyy, GETDATE()) AS VARCHAR(8))
) -- End Stuff
AS DATETIME),
data = right( data, len(data) -
LEN( REPLACE( CHAR(32) + CHAR(32), CHAR(32), --remove double spaces
LEFT( data, CHARINDEX( CHAR(32), data,
CHARINDEX( CHAR(32), data, 7) +1)) -- Skip to 7 for the JAN 1 structure, then find the tail.
) -- End Replace
) -1)
UPDATE #tmp
SETiprismsource = LEFT( data, 28),
data = RIGHT( data, len(data) - 30)
UPDATE #tmp
SET
IP2 = LEFT( data, CHARINDEX( ' ', data)-1),
data = RIGHT( data, len(data) - CHARINDEX( ' ', data))
FROM #tmp
UPDATE #tmp
SET
Staff_student = LEFT( data, CHARINDEX( ' ', data)-1),
data = RIGHT( data, len(data) - CHARINDEX( ' ', data))
FROM #tmp
UPDATE #tmp
SET
LoginName = LEFT( data, CHARINDEX( ' ', data)-1),
data = RIGHT( data, len(data) - CHARINDEX( ' ', data))
FROM #tmp
UPDATE #tmp
SET
SomeInt = CAST( LEFT( data, CHARINDEX( ' ', data)-1) AS INT),
data = RIGHT( data, len(data) - CHARINDEX( ' ', data))
FROM #tmp
UPDATE #tmp
SET
HTTPAddr = LEFT( data, CHARINDEX( ' ', data)-1),
data = RIGHT( data, len(data) - CHARINDEX( ' ', data))
FROM #tmp
-- We're going to reverse here, because corporate marketing, weblog(blog)
-- throws off the pattern here, so we'll work in from the back.
UPDATE #tmp SET data = REVERSE( data)
UPDATE #tmp
SET
pagetype = REVERSE( LEFT( data, CHARINDEX( ' ', data)-1)),
data = RIGHT( data, len(data) - CHARINDEX( ' ', data))
FROM #tmp
UPDATE #tmp
SET
SomeInt3 = CAST( REVERSE( LEFT( data, CHARINDEX( ' ', data)-1)) AS INT),
data = RIGHT( data, len(data) - CHARINDEX( ' ', data))
FROM #tmp
UPDATE #tmp
SET
HTTPType = REVERSE( LEFT( data, CHARINDEX( ' ', data)-1)),
data = RIGHT( data, len(data) - CHARINDEX( ' ', data))
FROM #tmp
UPDATE #tmp
SET
SomeInt2 = CAST( REVERSE( LEFT( data, CHARINDEX( ' ', data)-1)) AS INT),
data = RIGHT( data, len(data) - CHARINDEX( ' ', data))
FROM #tmp
UPDATE #tmp
SETSource = REVERSE( data),
data = ''
SELECT * FROM #tmp

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

SQL33 (11/2/2010)
It looks like this code combines the results of the first few columns and then parses them along with the last column (the MsgText http column) back out. I'm not entirely clear on why you needed to do that... and, I'm getting errors when I run it as a single query. Can you provide a little more explanation on what's going on with the various parts?
Thanks..

Alright, this ran fine earlier... and now I get errors. I must have miscopied something and am rechecking the code.

Code, in general, does the following:

Creates the temp data, and shows you the raw select at first.

Trims off the 'test message' and null rows.

It then alters the table to add a place to drop off the different components of the string.

Then it does a series of updates into the additional fields, which should be each component of the string you already have, for example:

UPDATE #tmp

--Char9 is a tab. Swap to Space

SET FirstDateTime = CAST( REPLACE( LEFT( data, 19), CHAR(9), CHAR(32) ) AS DATETIME),

data = RIGHT( data, len( data )- 20 )

This takes the first 19 characters, replaces the tab between the date and time with a space, and casts it as a datetime into the FirstDatetime field. It then trims off the 'field' it just removed from the data so the data that's left is without what we just removed.

Each update does this for a field, one by one, stripping the data down.

Will repost code when I figure out what I did here...

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

Figured out the problem. It's because the 'tabs' are converted to spaces when I copy/paste the code in. Seeing if it works from plain encoding. Yes, plain encoding kept the 'tabs' from your original data.

So, copy the first half here from the 'plain encoding', which builds the data as is from your load file. Then run the code in the second half.

DROP TABLE #tmp;

CREATE TABLE #tmp ( data VARCHAR(8000));