regex for matching string with multiple occurance in java

Question

I need a regex expresssion which match below strings in java.

select * from test

select * from test where a=100

select * from test where a=100 and b=100

the select statement may or may not contain where condition, also it may contain 1 to n number of conditions, i need a single regex to match all of those, can anyone help

i tried to match the string by using below regex expression, but it fails select (([A-Za-z0-9]{1,20}(|\\,))*|\\*) from ([A-Za-z0-9]{1,20})( where ([A-Za-z0-9]{1,20}((| )(\\=|\\>|\\<)(| ))[A-Za-z0-9]{1,20})|$)

Criteria:

i) a string should start with select

ii) if where is present it should be followed by a pair of variable and value

  like below:

        `select * from test where a=10;`

iii) Between two variable and value pair and should be present

  like below:
         `select * from test where a=10 and b=10`

iv) a string may or may not contain where

v) a string should not end with and

public class Test {
public static Pattern pattern;
public static Matcher matcher;

private static final String PATTERN="(\\s)*select(\\s)+((\\*)|([a-zA-Z0-9]+))(\\s)+from(\\s)+[a-zA-Z0-9]*(\\s)+where(\\s)+(‌[a-zA-Z0-9]*(\\s)*=(\\s)*[0-9]*)+(\\s)*((\\s)*and(\\s)*([a-zA-Z0-9]*(\\s)*=(\\s)*[0-9]*)‌)*(\\s)*";


public static void main(String[] args)throws IOException {
    BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
    String data;        
    pattern=Pattern.compile(PATTERN,Pattern.CASE_INSENSITIVE);
    do{         
    data=br.readLine();
    matcher=pattern.matcher(data);
    if(matcher.find()){
        System.out.print("True");
    }else{
        System.out.print("false");
    }
    System.out.print(":"+data+":\n");
    }while(data!="exit");
}

}

Test Cases(should show true)

select a from test

select a from test where a=10

select a from test where a=10 and b=10

select a from test where a=10 and b=10 and c=10

Test Cases(should show false)

select a from test where

select a from test where a=10 and

select a from test where a=10 b=10

select a from test where a=10 and b=10 and

thanks

You ought to make a start on this and submit your progress: that way we know how to gauge the answer detail. — Bathsheba
– Bathsheba, Commented May 15, 2014 at 7:30
Please put your regular expression in the actual question: it doesn't really belong in a comment; you'll attract downvoting the way things are. — Bathsheba
– Bathsheba, Commented May 15, 2014 at 7:34
So you just want to match each line beginning with select * from test, or you want to parse the conditions or...? Can you give an expected output? — Robin
– Robin, Commented May 15, 2014 at 7:38
its like sql select statment, it may be vary on each case. below are the example string select a from test select a,b from group select * from group where a=1 and b=10 etc — Pradeep
– Pradeep, Commented May 15, 2014 at 7:41

Community · Accepted Answer · 2017-05-23 12:22:14Z

0

I am new to Regular expressions. After reading this question i tried the following in online regex testers. it works fine.

(\s)*select(\s)+((\*)|([a-zA-Z0-9]+))(\s)+from(\s)+[a-zA-Z0-9]*(\s)+where(\s)+([a-zA-Z0-9]*(\s)*=(\s)*[0-9]*)+((\s)+and(\s)+([a-zA-Z0-9]*(\s)*=(\s)*[0-9]*))*(\s)*

I think it is too long. But it works fine for case 2 and case 3. If you want to achieve only these cases through regex , you need two regex to achieve your desired solution.

If you want to achieve all the three in single regex its not possible[According to my knowledge] .
As Bobbel mentioned in comments , Its not possible.

Because you need to check whether "where" is present or not . If present , it should have condition. Else "where" itself shouldn't be present.

EDIT

import java.util.regex.Pattern;
import java.util.regex.Matcher;
public class HelloWorld
{
  private static final String PATTERN="(\\s)*select(\\s)+((\\*)|([a-zA-Z0-9]+))   (\\s)+from(\\s)+[a-zA-Z0-9]*(\\s)+where(\\s)+([a-zA-Z0-9]*(\\s)*=(\\s)*[0-9]*)+ ((\\s)+and(\\s)+([a-zA-Z0-9]*(\\s)*=(\\s)*[0-9]*))*(\\s)*";

 public static void main(String []args)
 {
    System.out.println("Hello World");
    String data="select * from asdf where a  = 20 and ax =20";        
    Pattern pattern=Pattern.compile(PATTERN); 
    Matcher matcher=pattern.matcher(data);
    if(matcher.find())
    {
      System.out.print("True");
    }
    else
    {
      System.out.print("false");
    }
    System.out.print(":"+data+":\n");
 }

}

See screenshots

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered May 15, 2014 at 11:40

Gibbs

23.1k14 gold badges84 silver badges147 bronze badges

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

11 Comments

bobbel Over a year ago

It is also possible matching all three in one with something like this: select...([\s]+where...([\s]+and...)*)* - regex101.com/r/uY2iP0. But anyway, you are very strict with these expressions. What about IS NULL or <, >, >=, <=. I still don't know, what @Pradeep expects...

Gibbs Over a year ago

Yeah it's still possible. It ll be too large regex. He specifies only "and" . So i din't check for relational operators.

Gibbs Over a year ago

That's what i given above in that regex. It works even if you add 100 "and".

Pradeep Over a year ago

@user3168736: the regex which you provided is not satisfying my criteria which i mentioned in my question.

Gibbs Over a year ago

@Pradeep Try the following

(\s)*select(\s)+((\*)|([a-zA-Z0-9]+))(\s)+from(\s)+[a-zA-Z0-9]*(\s)+where(\s)+([a-zA-Z0-9]*(\s)*=(\s)*[0-9]*)+(\s)*((\s)*and(\s)*([a-zA-Z0-9]*(\s)*=(\s)*[0-9]*))*(\s)*

And could you please post your test case examples.

|

Pradeep · Accepted Answer · 2014-05-30 13:34:26Z

0

Finally i found solution for my question. this will match all my criteria.

select\s(([A-Za-z0-9]{1,20})(|\,[A-Za-z0-9]{1,20})+|\*)\sfrom\s([A-Za-z0-9]{1,20})(\swhere\s[A-Za-z0-9]{1,20}\=[A-Za-z0-9]{1,20}($|(\sand\s[A-Za-z0-9]{1,20}\=[A-Za-z0-9]{1,20})+$)|$)

Hope it will help anyone in future.

answered May 30, 2014 at 13:34

Pradeep

511 silver badge8 bronze badges

Collectives™ on Stack Overflow

regex for matching string with multiple occurance in java

2 Answers 2

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related