Regular Expressions Question 2

[jasonp45]

I have a number of lines of text in the following pattern:

"Create View [MyView] As [Some SQL]"

And these are interspersed with other lines of text. I want to identify all the 'Create View' lines, then use a regular expression that isolates the [MyView] and the [Some SQL] portions of the line as distinct variables.

I'm sure this is quite simple but I'm only vaguely familiar with regular expressions and am not having any luck. I'm reading in the text-file line by line, and I have created the RegExp object in the script and can ID & replace parts of the string but that's it.

Thanks.

 

[HarleyQuinn]

Using a pattern similar to

\[\b(.*?)\]

would pick out the bracketed words for you. Post back if you'd like an explanation of how that particular pattern works.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

Have a look here:

http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

That links getting a lot of use today PH

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jasonp45]

Thanks. Unfortunately I put the brackets in for clarity; they aren't actually in the lines of the text-file.

So the actual portions of each line I'm looking for are bracketed by "Create View" then " As ".

Anyway I solved it without using RegExp like this:

    While Not oFile.AtEndOfStream
      sLine = oFile.ReadLine
	    If InStr(sLine, "Create View") > 0 Then
        sLine = Replace(sLine, "Create View ", "")
        sQueryName = Left(sLine, InStr(sLine, " ")-1)
        sQueryText = Replace(sLine, sQueryName & " As ", "")
        Set oQueryDef = oDB.CreateQueryDef(sQueryName, sQueryText)
      End If	   
    Wend

But I'm sure it would be more elegant if I could figure out the regular expression.

 

[HarleyQuinn]

Jason, if VB supported Lookbehind this wouldn't be too bad, unfortunately it doesn't

Here is a fairly short RegExp solution to your problem. Let me know if you need any clarifiaction on any of it (it's mainly in VB6 but the syntax is pretty much identical):

Dim re As Object
Dim mc As Object
Dim m As Object
Dim strSearch As String

Set re = CreateObject("VBScript.RegExp")

strSearch = "Create View MyView As Some SQL Statements Etc. Etc. As"

With re
   .Global = True
   .MultiLine = True
   .IgnoreCase = True
   .Pattern = "\s\w*?(?=\sAs)|\sAs\s\w.*"

   For Each m In re.Execute(strSearch)
      .Pattern = "^As\b"
      MsgBox Trim(.Replace(Trim(m.Value), ""))
   Next m

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[strongm]

Dim strText As String
Dim re As RegExp
Dim MyMatch As Object

Dim sQueryName As String
Dim sQueryText As String

Set re = New RegExp

strText = "Create View MyView As Some SQL"
strText = strText & vbCrLf & "some random rubbish"
strText = strText & vbCrLf & "Create View MyView2 As Some More SQL that may or may not be legit"


re.Pattern = "Create View (.*?) As (.*?)$"
re.MultiLine = True
re.Global = True

For Each MyMatch In re.Execute(strText) ' feed ALL your text to this
sQueryName = MyMatch.SubMatches(0)
sQueryText = MyMatch.SubMatches(1)
' Do your thing here ...
' e.g
MsgBox sQueryName & ", " & sQueryText
Next

 

[HarleyQuinn]

Nice, i'd forgotten about the submarches collection in my preocupation with that pesky lookbehind

I am coming around to your way of thinking regarding the usefulness of regular expressions though

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[strongm]