CODEWITHSUNDEEP
sqlvb.netregextext-parsing
Mithil
Mithil

Reputation: 3770

How to create a regular expression to parse my SQL statement

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?

Upvotes: 1

Views: 1370

Answers (4)

Jonathan Webb
Jonathan Webb

Reputation: 1573

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

Upvotes: 0

Michael G
Michael G

Reputation: 6755

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

Upvotes: 1

Albert
Albert

Reputation: 546

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

Upvotes: 0

Fabian Steeg
Fabian Steeg

Reputation: 45754

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

Upvotes: 1

Related Questions