VBScript Multi Line Pattern Match

I am trying to extract a SQL statement from an email. The problem is that I don't know if the email message will have a signature line or not.  

So, in the code section I would want everything before two carriage returns in a row.

The ideal match would be...

select *
from smr_retl_cnt_stg
with ur

I tried
((?:.+\r\n)*)\r\n\r
but I wasnt getting the expected results

select *
from smr_retl_cnt_stg
with ur
 
 
Chris G x83061
----------------------------
Sent from my BlackBerry Wireless Handheld

Open in new window

LVL 4
orbitusAsked:
Who is Participating?
 
ddrudikConnect With a Mentor Commented:
From testing with http://regexpal.com/ the following matches your source text copied from above:
^[\S\s]*?(?=\r\n(?:\x20\r\n){2}|$)

Open in new window

0
 
ddrudikCommented:
In ASP/VBSCRIPT with your sample that would be:
<%
Set regEx = New RegExp
regEx.Pattern = "^[\S\s]*?(?=\r\n(?: \r\n){2}|$)"
Set Matches = regEx.Execute(yourstring)
%>

Open in new window

0
 
orbitusAuthor Commented:
Hi ddrudik, I am using the VB script engine in Office VBA. Identical to the Javascript engine.  Your string does not seem to work with that engine.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
orbitusAuthor Commented:
When I go to that site and enter all the information I see a match to the entire statement. Am I not configuring the site correctly to run the test?
0
 
ddrudikCommented:
Then it is likely that your source text does not match the copy-and-paste that I took from your question.  Consider copying/pasting another example here for testing.
0
 
orbitusAuthor Commented:
The engines must be different. This pattern works in VBA

((?:.+\r?\n)*)\r?\n\r?\n

All I had to do to fix my own was put in the optional handling of the carriage returns.

Thanks for the help though.
0
 
ddrudikCommented:
If that is the case then your source differed from the sample, which had \x20\r\n in it.
0
 
orbitusAuthor Commented:
Using this VBA code, just posting for reference.
Dim myMatch As Match
Dim myMatches As MatchCollection
Dim myRegExp As RegExp
Set myRegExp = New RegExp
myRegExp.IgnoreCase = True
myRegExp.Pattern = "((?:.+\r?\n)*)\r?\n\r?\n"
Set myMatches = myRegExp.Execute(strSQL)
For Each myMatch In myMatches
	'matched text: myMatch.Value
	'match start: myMatch.FirstIndex
	'match length: myMatch.Length
	For I = 1 To myMatch.SubMatches.Count
		'backreference text: myMatch.SubMatches(I-1)
	Next
Next

Open in new window

0
 
orbitusAuthor Commented:
What is \x20, I don't see that?
0
 
orbitusAuthor Commented:
Here is another sample...

select *
from smr_retl_cnt_stg
with ur
 
 
Chris G x83061
----------------------------
Sent from my BlackBerry Wireless Handheld


I was looking to get just the SQL and not the signature.
0
 
ddrudikCommented:
\x20 is the hexadecimal notation for the space character.
\xnn is the general syntax, here nn can be any character hex value, such as those you see in the ASCII table: http://www.asciitable.com/
0
 
ddrudikCommented:
here's an example from http://regexpal.com
img.jpg
0
 
orbitusAuthor Commented:
Im sorry, Google Chrome and the EE site were the culprt.  The EE site keeps putting spaces on the blank lines after the SQL statement, those should not be there.  Google chrome was making regexpal not pull up any matches.

When I move over to Internet Explorer your pattern works great with the lines with spaces but not when those spaces are removed.
0
 
ddrudikCommented:
Have you resolved your original question?
0
 
orbitusAuthor Commented:
Yes, this pattern works for me...

((?:.+\r?\n)*)\r?\n\r?\n

Your pattern obviously works with the information you pulled from the site so I'm closing  the request as you solved it.  Thanks for your help.
0
 
ddrudikCommented:
Thanks for the question and the points.
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.

All Courses

From novice to tech pro — start learning today.