Clever Geek Handbook

Regular expression to match "select ... from ... where" sql query

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

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

should be compared.

Here is the regex pattern I wrote:

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

But that won't work. What's wrong with him?

+2


source to share


6 answers


You don't have to have a backslash; since you did, the engine tries to match a literal point, not "any character" as you would expect.

Try:



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

Also, like the others posted, make sure to be in case insensitive mode. How you do this depends on the language you are using.

+8


source


I'm not sure which regex engine you are targeting, but you can 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
+5


source


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

+1


source


Assumptions:

  • Your sql statement will not span rows.
  • You won't have two sql statements on the same 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 can add a terminator instead. * depending on your case. Of course, you have to run it in case insensitive mode, or change the regex accordingly.

0


source


Thanks guys answer. I got a response 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)
0


source


Try this regex, I tried to be a little more strict with the table and column names, and also consider using a filter with (=, <=,> =, <,> and IN ()):

string regex = "SELECT\W(([a-zA-z0-9]+)([,]*)\W)+\WFROM\W([a-zA-z0-9#]+)(\W[a-zA-Z])*\WWHERE\W([a-zA-z0-9_]+)\W([=<>IN(]+)\W(.+)"
0


source






More articles:


All Articles