RegEx

Hi,

I want to use RegEx to parse a text string (I want to extract the tables
referenced in a SQL statement.)

The definition of the search pattern can be described as

find "FROM" or "JOIN" AND 'followed by
(word.word.word)
OR (word..word) 'yes, that's 2 dots
OR (word.word)
OR (..word)
OR (.word)
OR (word)
and to capture that pattern into a variable.

Example:
strText = "select * from TableName1 inner join asdf.TableName2 on something
and thing join Table3 on "
i need to extract
TableName1
asdf.TableName2
Table3

I have tried to experiment with this but can't seem to get it right.
What am I doing wrong? see code below

Thanks in advance for your assistance
Regards
HS

------------------------------------------------------------------

Sub ParseSQL()


Dim regex, Match, Matches, Patrn, strText ' Create variable.
'Patrn = "(\bFrom|Join\b)([\w\.]*){1,3}\b"
'Patrn = "(\bFrom|Join\b)(\w*\.){0,2}(\w*)\b"
Patrn = "(([A-Z][A-Z0-9_]*)\.{0,2})(\w*)\b"


strText = "select * from TableName1 inner join asdf.TableName2 on
something and thing join Table3 on "
' strText = "select * from dbo.TableName1 inner join .TableName2 on
something join ABCDB..Table3 on "

Set regex = New RegExp ' Create a regular expression.
regex.Pattern = Patrn ' Set pattern.
regex.IgnoreCase = True ' Set case insensitivity.
regex.Global = True ' Set global applicability.


Set Matches = regex.Execute(strText) ' Execute search.
Debug.Print "Num matches: " & Matches.Count

For Each Match In Matches ' Iterate Matches collection.
retstr = " Pos: " & Match.FirstIndex & "; Val: " & Match.Value & ";"

If Match.SubMatches.Count > 0 Then
For i = 0 To Match.SubMatches.Count - 1
retstr = retstr & "Sub(" & i & ")= " & Match.SubMatches(i) & ";"
Next
End If

Debug.Print retstr
'Debug.Print Match.SubMatches(1)
Next
End Sub



.

Relevant Pages

  • Re: Regex question
    ... structure of the date you're trying to extract. ... For example, in Regex you can ... pattern that will ensure a valid date within the range allowed by T-SQL ... valid date from a string. ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: HowTo? RegEx - pattern to exclude the whole word
    ... I am trying to extract the pattern like this: ... SUB: this text I want to extract LOT: 2345, something in between, new ... Obviously, something in this regex tells it to be "greedy", and I need ...
    (microsoft.public.dotnet.general)
  • RE: help in variable pattern matching
    ... can I have a $pattern that contain some special characters ... to pass it into regex? ... I want to extract the line that contains either ...
    (perl.beginners)
  • RE: RegEx HELP!!!
    ... Assuming the pattern starts with an XY and then is followed by one or more ... *digitsequence, the following RegEx can be used to extract the specified ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: Extract the desired value from a regex
    ... Extract the desired value from a regex ... property of Persistent Systems Ltd. ... Systems Ltd. does not accept any liability for virus infected mails. ...
    (perl.beginners)