string parsing

[GROKING]

I have a table in access with strings like this in a text column.
I want to create a query that trims off the "

http://www."

and everything to the right of ".com" leaving just "model.com". I know I need to use instr and mid to do this but not sure how to do it all in so I can append this to a new blank column.

http://www.model.com/?source=overture&kwid=1/100th scale model of b 52

Any ideas would be very helpful??

Thanks

 

[ddiamond]

create the following VBA function:

public function ParseURL(byval sURL as variant) as string
  Dim lStart as long
  Dim lEnd as long
  
  lStart=instr(sURL & "","[URL unfurl="true"]www.",vbtext)[/URL]
  lEnd = instr(sURL & "",".com",vbtext)
  ParseURL = mid(sURL & "",lStart+4,lEnd-lStart+1)
end sub

 

[ddiamond]

Correction:

public function ParseURL(byval sURL as variant) as string
  Dim lStart as long
  Dim lEnd as long
  
  lStart = instr(1,sURL,"[URL unfurl="true"]www.",vbTextCompare)[/URL]
  lEnd = instr(1,sURL,".com",vbTextCompare)
  ParseURL = mid(sURL & "",lStart+4,lEnd-lStart+1)
end sub

You may want to add some special case logic if either lStart or lEnd is 0 meaning that it did not contain a

http://www or

a com.

 

[JerryKlmns]

Public Function GetTheRest(strURL As String) As String

Select Case True
Case Left(strURL, 7)= "

http://"

strURL = Right(strURL, Len(strURL) - 7)
Case Left(strURL, 6)= "ftp://"
strURL = Right(strURL, Len(strURL) - 6)
End Select
If Left(strURL, 4)= "

http://www."

Then
GetTheRest = Right(strURL, Len(strURL) - 4)
Else
GetTheRest = strURL
End If
End Function

 

[ddiamond]

Jerry,

I like your function. The only suggestion I would make is to make strURL a byval variant because this function is going to be used within an sql query. You wouldn't want it to bomb if the field was null.

- Dan

Public Function GetTheRest(byval strURL As variant) As String

strURL = trim(strURL & "")

Select Case True
Case Left(strURL, 7)= "

http://"

strURL = Right(strURL, Len(strURL) - 7)
Case Left(strURL, 6)= "ftp://"
strURL = Right(strURL, Len(strURL) - 6)
End Select
If Left(strURL, 4)= "

http://www."

Then
GetTheRest = Right(strURL, Len(strURL) - 4)
Else
GetTheRest = strURL
End If
End Function

 

[JerryKlmns]

Your mentioning of *null* brings up errors in Select Case
part. We should add at the very top

If Len(strURL & "") < 8 Then
GetTheRest = ""
Exit Function
End If