How to 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?

what "flavor" of regex are you using, in what environment? ie, Perl, .NET, etc. — Michael Paulukonis
– Michael Paulukonis, Commented Feb 26, 2009 at 15:50

Svante · Accepted Answer · 2009-02-26 16:00:52Z

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

edited Feb 26, 2009 at 16:00

Svante

51.8k11 gold badges84 silver badges127 bronze badges

answered Feb 26, 2009 at 15:52

Fabian Steeg

45.9k7 gold badges88 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Mithil Over a year ago

This one works but I don't want the word "Order" i want everything starting from the word "FROM" till the word "order" excluding the word "Order" also i want it case insensitive.

Chris Farmer Over a year ago

isn't that what the (parentheses) are for?

Mithil Over a year ago

It still gives me " FROM codes WHERE FieldName='ContactMethod' and IsNull(Deactived,'') != 'T' order"

cdmckay Over a year ago

The first match (the stuff in brackets) will be the part without order. Read up about RegEx matches.

Jeff Moser Over a year ago

To not have order show up in the match, do this "(FROM.*)(?=order)"

|

Michael G · Accepted Answer · 2009-02-26 16:17:26Z

1

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

answered Feb 26, 2009 at 16:17

Michael G

6,7752 gold badges45 silver badges60 bronze badges

Comments

Albert · Accepted Answer · 2009-02-26 16:00:57Z

0

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

answered Feb 26, 2009 at 16:00

Albert

5463 gold badges7 silver badges16 bronze badges

2 Comments

Mithil Over a year ago

yup..that solves the case sensitive issue. I am still getting the word "Order" which i don't want. I am using (from|FROM.*)order|ORDER

Ben Blank Over a year ago

Nearly all regex flavors support a case-insensitivity flag.

Jonathan Webb · Accepted Answer · 2009-02-26 16:24:07Z

0

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

answered Feb 26, 2009 at 16:24

Jonathan Webb

1,5731 gold badge17 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to create a regular expression to parse my SQL statement

4 Answers 4

8 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related