Extracting text inside <> brackets from a text box 3

[ftpdoo]

Hi,

I have a text box (txtText) which contains an SQL statement as follows:

INSERT INTO myTable VALUES(<WorkOrder>, <FreeText>, <Fixed>)

I'm trying to extract the values inside the angled brackets (ie WorkOrder, FreeText, Fixed) and to place these values into three separate text boxes.

Thankx in advance,
Jonathan

 

[RichieSimonetti]

It is not a good idea use those symbols since it means major to and minor to in sql statements (and in math too).
Could you replace them with other ones?

 

[LegoArtist]

The only way I know of to do that is with two InStr statements. Find out where the first < is and look for the > after that position. Then read the text between those two points. Look for the second < after the first >, and so on.

Here's some quick code to show what I mean. I'm assuming that there will be 3 and only 3 pairs of <>. You could also do this with arrays and loops (which might be better) but this way it's easier to see what's going on.

Private Sub ExtractInfo (Byval strSqlStatement As String)
Dim intLeftPosition As Integer ' where the < is
Dim intRightposition As Integer ' where the > is

' find the first pair
intLeftPosition = InStr(strSqlStatement, &quot;<&quot
intRightposition = InStr(intLeftPosition + 1, strSqlStatement, &quot;>&quot;
text1.Text = Mid$(strSqlStatement, intLeftPosition + 1, intRightPosition - intLeftPosition - 1)

' find the second pair
intLeftPosition = InStr(intRightPosition + 1, strSqlStatement, &quot;<&quot
intRightPosition = InStr(intLeftPosition + 1, strSqlStatement, &quot;>&quot
Text2.Text = Mid$(strSqlStatement, intLeftPosition + 1, intRightPosition - intLeftPosition - 1)

' find the third pair
' exactly the same code as for the second pair
End Sub

Also notice that I'm not checking the value of any variables. You might want to do that in your real code .

 

[JohnYingling]

Function ExtractTags(strSql as string) as string()
'Returns array with 0th element unused.
' < and > included. 
'To drop <>, strW = Mid$(aryStr(I),2,Len(aryStr(I)-2)
Dim I as long
Dim J as long
Dim L as long
Dim K as long
Dim aryStr() as string
K = 0
Redim aryStr(0) ' Ubound(aryStr) = # of items.
J = 1
L = Len(strSql)
Do 
    I = Instr(J,strSql,&quot;<&quot;)
    if I = 0 then exit do
    J = Instr(I,strSql,&quot;>&quot;)
    if J = 0 then exit do
    K = K + 1
    Redim Preserve aryStr(K)
    aryStr(K) = Mid$(strSql,I,J-I+1)
    I = J + 1
Loop
ExtractTags = aryStr ' 0-based, 0 element unused

http://www.VBCompare.com

http://www.VBSortGen.com

 

[strongm]

Someone was asking in this forum for examples of occassions where Regular Expressions might be appropriate. This seems to be such an occasion, so I've taken the liberty of rewriting JohnYingling's example to use them. You'll need to add a reference to the Microsoft VBScript Regular Expressions library.
[tt]
' Returns array cotaining strings extracted from between brackets
Private Function ExtractTags(strSql As String) As String()
Dim re As RegExp
Dim myMatchCollection As MatchCollection
Dim myMatch As Match
Dim aryStr() As String

ReDim aryStr(0)
Set re = New RegExp
re.Global = True
re.Pattern = &quot;<(.*?)>&quot;
Set myMatchCollection = re.Execute(strSql)
For Each myMatch In myMatchCollection
aryStr(UBound(aryStr)) = myMatch.SubMatches(0)
ReDim Preserve aryStr(UBound(aryStr) + 1)
Next
ReDim Preserve aryStr(UBound(aryStr) - 1)
ExtractTags = aryStr
End Function

 

[JohnYingling]

If there are no matches then
ReDim Preserve aryStr(UBound(aryStr) - 1)
will fail with &quot;subscript out of range&quot;.

Change
ReDim aryStr(0)
to
ReDim aryStr(1)

http://www.VBCompare.com

http://www.VBSortGen.com

 

[strongm]

True. There's a fair amount of other error checking that could be in there...