Please help me create a regular expression to parse my SQL statement

Question

I want to extract

FROM codes WHERE FieldName='ContactMethod' and IsNull(Deactived,'') != 'T'

from

SELECT FieldDescription,FieldValue FROM codes WHERE FieldName='ContactMethod'
   and IsNull(Deactived,'') != 'T' order by fielddescription

using a regular expression. I have a regex like this:

\FROM.*\order

which extracts

FROM codes WHERE FieldName='ContactMethod' and IsNull(Deactived,'') != 'T' order

Also, how can I get rid of the capitalization?

Thanks

Answer 1

The trick here would probably be to capture the part you actually want with parens:

(FROM.*) order

This would greedily match until the last order, if you want only until the first occurrence, match lazily:

(FROM.*?) order

Answer 2

If it comes down to it you can ignore capitalization by doing (F|f)(R|r)(O|o)(M|m).

Answer 3

Expanding on Fabian Steeg's answer

 Dim regex As Regex = New Regex( _
          "(FROM.*?) ORDER", _
        RegexOptions.IgnoreCase _
        Or RegexOptions.CultureInvariant _
        Or RegexOptions.IgnorePatternWhitespace _
        Or RegexOptions.Compiled _
        )

    Dim ms As MatchCollection = regex.Matches(InputText)

where InputText is of course your SQL query string.

ms(1) should hold the parentheses match

Answer 4

Interactive tools like RegexBuddy ($40) or The Regex Coach (free) would really help you to design and debug regular expressions for most platforms.