Parsing Problem

[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

 

[John Spencer]

You might take a look at using a custom VBA function using the split
function. The following untested function should give you the idea.

Function fGetURLRoot(strIn)
Dim v As Variant

If Len(strIn & "") = 0 Then
fGetURLRoot = strIn
Else
v = Split(strIn, "/")
If UBound(v) < 3 Then
fGetURLRoot = strIn
Else
fGetURLRoot = v(0) & "//" & v(2) & "/" & v(3) & "/"
End If
End If

End Function



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[robboll]

You might take a look at using a custom VBA function using the split
function. The following untested function should give you the idea.

Function fGetURLRoot(strIn)
Dim v As Variant

If Len(strIn & "") = 0 Then
fGetURLRoot = strIn
Else
v = Split(strIn, "/")
If UBound(v) < 3 Then
fGetURLRoot = strIn
Else
fGetURLRoot = v(0) & "//" & v(2) & "/" & v(3) & "/"
End If
End If

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have table URLsamp with a column uri as follows:

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- Hide quoted text -

- Show quoted text -

Well - I checked out your work. Works like a Champ! Thanks John!