vb.net regular expressions between two words

Question

vb.net regular expressions between two words

Lowell

SSC Guru

Points: 323518
More actions
January 22, 2010 at 6:13 pm

#219834

can anyone point out my logic hole here? I've googled around, and cannot seem to get any example to work so far.
I'm trying to capture whatever is between two words; my example here is whatever is between "select" and "from", which might be an asterisk, or a string of column names, etc.
i think my pattern should be this:
"\bselect\b(.*?)\bfrom\b"
as i understand it \b will designate a word boundary, so i don't get words that happen to contain "from", like "fromdate" or anything like that. i've tried a number of different patterns so far,which you can review below.
the stuff in parenthesis should return one or more characters between the two words,right?
the statement could have multiple CrLf in it, and that might be why I'm not matching, but I'm not seeing it yet...i thought that the option System.Text.RegularExpressions.RegexOptions.Multiline allowed me to search multi line strings.
in a given statement, there might be more than one group of select...from, so i need to loop thru each match.
throw this code in a button event of a windows application to test:
Dim rm As System.Text.RegularExpressions.MatchCollection
'Dim m As System.Text.RegularExpressions.Match
Dim pattern As String
Dim sql As String
sql = "select * " & vbCrLf
sql = sql & "from syscolumns " & vbCrLf
sql = sql & "where id in(select id " & vbCrLf
sql = sql & " from sysobjects " & vbCrLf
sql = sql & " where xtype='U') " & vbCrLf
pattern = "\(select\).*?\(from\)"
pattern = "\b(select)\b(.*?)\b(from)\b"
pattern = ".*select (.+) from.*"
pattern = "\bselect\b(.*?)\bfrom\b"
' pattern = "\(select\).*?\(from\)"
rm = System.Text.RegularExpressions.Regex.Matches(sql, pattern, System.Text.RegularExpressions.RegexOptions.IgnoreCase Or System.Text.RegularExpressions.RegexOptions.Multiline)
For Each mt As System.Text.RegularExpressions.Match In rm
Debug.Print(mt.Value)
Next
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

discovered that my problem was the period in the pattern...the period matches an character except a new line...the patter i'm using, if anyone else ever needs a regular expression to capture between two words,including those two words, is this:

pattern = "\bselect\b([\s\S]*?)\bfrom\b"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!