Help me with a simple regular expression

Question

Hello,

I want to find a sequence of "select from where " in a string. For example,

"select * from T WHERE t.c1 =1"

should be matched.

Here is the regular expression pattern I wrote:

"SELECT\s\.*FROM\s\.*WHERE\s\.*";

But it doesn't work. What is wrong with it?

Thanks in advance.

Answer 1

You shouldn't have backslash-escaped the dots; since you did, the engine is trying to match a literal dot, not "any character" like you're expecting.

Try:

SELECT\s.*FROM\s.*WHERE\s.*

Also, as others have posted, make sure it's in case-insensitive mode. How you do that depends on the language you're using.

Answer 2

I'm not sure what regex engine you're targeting, but you might try this:

# note the trailing i, which in perl means case-insensitive
# this will also put the interesting bits into regex backreferences
#
# This also changes \s to \s+ in case you have a situation with multiple
# spaces between terms
#
/select\s+(.*)\s+from\s+(.*)\s+where\s+(.*)/i

Answer 3

The problem with the RE is probably that it's case sensitive. Depending on the form of RE, there is probably . For example, Perl-compatible REs use a "/i" flag: /SELECT\s.*FROM\s.*WHERE\s.*/i

Answer 4

Assumptions:

• Your sql statement will not span across lines.
• You will not have two sql statements on a single line.

This works for me.

SELECT\s+?[^\s]+?\s+?FROM\s+?[^\s]+?\s+?WHERE.*

Java escaped version:

String regex = "SELECT\\s+?[^\\s]+?\\s+?FROM\\s+?[^\\s]+?\\s+?WHERE.*";

You may append a terminator instead of the .* depending on your case. Of course, you have to run it in case-insensitive mode, OR modify the regex appropriately.

Answer 5

Thanks your guys's reply. I got the answer from mmyers, here is my final solution:

        string szPattern = @"SELECT\s.*FROM\s.*WHERE\s.*";
        Regex  rRegEX = new Regex ( szPattern,RegexOptions.IgnoreCase | RegexOptions.Multiline );
        Match match =rRegEX.Match(testCase.Statement);
        if (match.Success)