Problem finding pattern matching and parsing it.

I have a string "EXEC (@SQL1 + @SQL2 + @SQL3)". My task is to find all substrings starting with '@'. Having to find such a string, I need to write that string to the database. For example, write '@SQL1', '@SQL2', and '@SQL3' to the database. The writing part to the database is not a problem. The hard part is finding the matching pattern.

Hope someone could help me with that.
Who is Participating?

Commented:
Here's a little token parser I've developed and used for years:

Public Function Toke\$(Inst\$, Sep\$)
'takes off first substring in inst\$, shortening inst\$ each time. Destructive to Inst\$.
Dim i As Integer
i = InStr(Inst\$, Sep\$)
If i = 0 Then
Toke\$ = Inst\$
Inst\$ = ""
Else
Toke\$ = Left\$(Inst\$, i - 1)
Inst\$ = Mid\$(Inst\$, i + Len(Sep\$))
End If
End Function

Using this function you would parse your sample data like this:

ParseWrite "EXEC (@SQL1 + @SQL2 + @SQL3)"

sub ParseWrite(istr\$)
dim x\$,y\$
y\$=istr\$  'because toke\$ is destructive
x\$=toke\$(y\$,"(") 'istr gets "EXEC (" peeled off
y\$=toke\$(y\$,")") 'now we have just @SQL1+@SQL2+@SQL3 in y\$
while len(y\$)>0
x\$=toke\$(y\$,"+")
'write x\$ value to database
wend

end sub
0

Commented:
Hi
Private Sub Command1_Click()
Dim nStart As Integer, nEnd As Integer, i As Integer
Dim s As String, sqlArr() As String
s = "EXEC (@SQL1 + @SQL2 + @SQL3)"
nEnd = 1
Do
i = i + 1
nStart = InStr(nEnd, s, "@") + 1
If nStart = 1 Then Exit Do
nEnd = InStr(nStart, s, "+") - 1
If nEnd < 0 Then nEnd = Len(s)
ReDim Preserve sqlArr(i)
sqlArr(i) = Mid\$(s, nStart, nEnd - nStart)
Loop
End Sub
Cheers
0

Commented:
Private Sub Command1_Click()
Dim s As String
Dim outstr As String
Dim nextpos As Long

s = "EXEC (@SQL1 + @SQL2 + @SQL3)"
' Remove everything up til first @
s = Mid\$(s, InStr(1, s, "@"))
' Remove last ')' and append a ' +'
s = Mid\$(s, 1, InStr(1, s, ")") - 1) & " +"

nextpos = InStr(nextpos + 1, s, "@")
Do Until nextpos = 0
outstr = Mid\$(s, nextpos, InStr(nextpos + 1, s, " +") - nextpos)
'insert outstr into table
nextpos = InStr(nextpos + 1, s, "@")
Loop
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.