October 16, 2003 at 3:42 am
Hello
I've got some tables with text fields wich contain sentences with "hard coded" urls.
I mean : blabla nla bla bla http://www.microsoft.com bla bla
I'm seeking some code or piece of code (or idea ?) to convert this sentence in :
blabla nla bla bla <a href=http://www.microsoft.com target=_blank>http://www.microsoft.com</a> bla bla
I precise that the record can have more than 1 occurence of URLs...
Thanks by advance for any given idea.
Matthieu
October 16, 2003 at 4:50 am
Hi Matthieu,
quote:
I'm seeking some code or piece of code (or idea ?) to convert this sentence in :
not sure if this is what you want for it is in VB, but maybe you'll get an idea. This procedure takes the string to be search as an input parameter and - in my case - extracts and inserts the urls into a separate table. That means it is done at the client before entering SQL Server. Modifying it so that it adds <a href...> </a> shouldn't be too difficult
Public Sub ExtractURL(oConn As ADODB.Connection, lpszIdentifier As String)
On Error GoTo err_handler
Const cURLMAX = 20
Dim oRecSet As ADODB.Recordset
Dim i As Integer
Dim j As Integer
Dim lPosStart() As Long
Dim lPosStartOld As Long
Dim lPosStop1() As Long
Dim lPosStop2() As Long
Dim lPosStop3() As Long
Dim lPosStop4() As Long
Dim lLen As Long
Dim lPosLen As Long
Dim lpszWebAdress() As String
Dim lpszSQL As String
ReDim lPosStart(1 To cURLMAX)
ReDim lPosStop1(1 To cURLMAX)
ReDim lPosStop2(1 To cURLMAX)
ReDim lPosStop3(1 To cURLMAX)
ReDim lPosStop4(1 To cURLMAX)
ReDim lpszWebAdress(1 To cURLMAX)
Set oRecSet = New ADODB.Recordset
lpszSQL = "SELECT * FROM extracted_urls WHERE id=0"
oRecSet.Open lpszSQL, oConn, adOpenDynamic, adLockOptimistic
lPosStartOld = 1
j = 1
For i = 1 To cURLMAX
lPosStart(i) = InStr(lPosStartOld, lpszIdentifier, "http://", vbBinaryCompare)
If lPosStart(i) > 0 Then
lPosStop1(i) = InStr(lPosStart(i), lpszIdentifier, " ", vbBinaryCompare)
lPosStop2(i) = InStr(lPosStart(i), lpszIdentifier, vbCrLf, vbBinaryCompare)
lPosStop3(i) = InStr(lPosStart(i), lpszIdentifier, ">", vbBinaryCompare)
lPosStop4(i) = InStr(lPosStart(i), lpszIdentifier, ")", vbBinaryCompare)
If lPosStop1(i) > 0 And lPosStop1(i) > lPosStart(i) Then
lPosLen = CNum.GetMaximum(lPosStart(i), lPosStop1(i))
End If
If lPosStop2(i) < lPosLen And lPosStop2(i) > 0 Then
lPosLen = CNum.GetMinimum(lPosStop2(i), lPosLen)
End If
If lPosStop3(i) < lPosLen And lPosStop3(i) > 0 Then
lPosLen = CNum.GetMinimum(lPosStop3(i), lPosLen)
End If
If lPosStop4(i) < lPosLen And lPosStop4(i) > 0 Then
lPosLen = CNum.GetMinimum(lPosStop4(i), lPosLen)
End If
If lPosLen > lPosStart(i) Then
lpszWebAdress(j) = Mid(lpszIdentifier, lPosStart(i), lPosLen - lPosStart(i))
j = j + 1
lPosLen = 0
End If
Else
Exit For
End If
lPosStartOld = lPosStart(i) + 1
Next i
j = 1
For j = 1 To UBound(lpszWebAdress)
If lpszWebAdress(j) <> "" Then
With oRecSet
.AddNew
.Fields("urladress") = Left$(lpszWebAdress(j), 255)
.Update
End With
End If
Next j
oRecSet.Close
Set oRecSet = Nothing
ReDim lpszWebAdress(1)
err_handler:
Err.Clear
Exit Sub
End Sub
Hope it is of some value
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply