R
robboll
I have table URLsamp with a column uri as follows:
uri
http://a829.ac-ges.myspacecdn.com/images02/69/l_f40bba6e8c040cee442b8fd57df5134.jpg
http://photo.doulike.com/nphoto/SmallPhoto/0/4/4867.JPEG
http://c5.zedo.com/bar/v14-100/c5/jsc/profile.js
I would like to go delete everything in the "url" field except for the
root domain name. That is everything after the third "/"
So "http://c5.zedo.com/bar/v14-100/c5/jsc/profile.js" would become
"http://c5.zedo.com/bar/" .
I have a crazy solution, but it works! :
SELECT UrlSAMP.uri, Mid([uri],1,InStr(1,[uri],"//")
+2+Len(Mid([uri],InStr(1,[uri],"//")+2,InStr(1,Mid([uri],InStr(1,
[uri],"//")+2,200),"/")))+Len(Mid(Mid([uri],InStr(1,[uri],"//")
+2+Len(Mid([uri],InStr(1,[uri],"//")+2,InStr(1,Mid([uri],InStr(1,
[uri],"//")+2,200),"/"))),50),1,InStr(1,Mid([uri],InStr(1,[uri],"//")
+2+Len(Mid([uri],InStr(1,[uri],"//")+2,InStr(1,Mid([uri],InStr(1,
[uri],"//")+2,200),"/"))),50),"/")))-1) AS uriTrunc
FROM UrlSAMP;
These are old parsing functions I am using: mid, instr, etc. Does
anyone have a better method? Thanks for any suggestions.
RBolling
uri
http://a829.ac-ges.myspacecdn.com/images02/69/l_f40bba6e8c040cee442b8fd57df5134.jpg
http://photo.doulike.com/nphoto/SmallPhoto/0/4/4867.JPEG
http://c5.zedo.com/bar/v14-100/c5/jsc/profile.js
I would like to go delete everything in the "url" field except for the
root domain name. That is everything after the third "/"
So "http://c5.zedo.com/bar/v14-100/c5/jsc/profile.js" would become
"http://c5.zedo.com/bar/" .
I have a crazy solution, but it works! :
SELECT UrlSAMP.uri, Mid([uri],1,InStr(1,[uri],"//")
+2+Len(Mid([uri],InStr(1,[uri],"//")+2,InStr(1,Mid([uri],InStr(1,
[uri],"//")+2,200),"/")))+Len(Mid(Mid([uri],InStr(1,[uri],"//")
+2+Len(Mid([uri],InStr(1,[uri],"//")+2,InStr(1,Mid([uri],InStr(1,
[uri],"//")+2,200),"/"))),50),1,InStr(1,Mid([uri],InStr(1,[uri],"//")
+2+Len(Mid([uri],InStr(1,[uri],"//")+2,InStr(1,Mid([uri],InStr(1,
[uri],"//")+2,200),"/"))),50),"/")))-1) AS uriTrunc
FROM UrlSAMP;
These are old parsing functions I am using: mid, instr, etc. Does
anyone have a better method? Thanks for any suggestions.
RBolling