Regex - SQL statement

Question

It is only a very simple SQL parsing process, no HAVING, ORDER BY or GROUP BY would be in the statement that needs to be parsed, but something about matching an asterisk in SELECT part won't work (e.g. SELECT * .......)

Here's the code part:

String span="SELECT * FROM table1 t, table2";

span=span.toUpperCase();

System.out.println(span);

// Segments
String column="(\\w+\\s*(\\w+\\s*){0,1})";

String columns=column+"(,\\s*"+column+")*";

String ownerenable="((\\w+\\.){0,1}\\w+\\s*(\\w+\\s*){0,1})";

String ownerenables=ownerenable+"(,\\s*"+ownerenable+")*";

String from="FROM\\s+"+columns;

String condition="(\\w+\\.){0,1}\\w+\\s*(=|<|>|LIKE|IS)\\s*'?(\\w+\\.){0,1}[\\w%]+'?";

String conditions=condition+"(\\s+(AND|OR)\\s*"+condition+"\\s*)*";

String where="(WHERE\\s+"+conditions+"){0,1}";

String pattern = "SELECT\\s+(\\*|" + ownerenables + "\\s+" + from + ")\\s+" + where + "\\s*" + "(;){0,1}";

System.out.println(pattern);
System.out.println(span.matches(pattern));

Now if I set span to something like

"SELECT t.id, product p FROM table1 t, table2 WHERE t.id=10;"

The match result would be true

================================

But if I set it to something like this:

"SELECT * FROM table1 t, table2 WHERE t.id=10;"

(It's an asterisk I'm using for the SELECT)

It won't work in this case

Am I doing anything wrong about that asterisk?

I'm not convinced that a regexp is the right tool to use here... — Maurice Perry
– Maurice Perry, Commented Dec 8, 2016 at 12:48
@Maurice Perry Yes...But I'm just doing a simple test here and the problem came.... — RushSykes
– RushSykes, Commented Dec 8, 2016 at 12:51
@RushSykes since * is a meta-character it should be backslashed "\"* . It will match *. You need to add it to your ownerenable. However it will be quite tricky to do it with regex — Anton Balaniuc
– Anton Balaniuc, Commented Dec 8, 2016 at 13:01

Maurice Perry · Accepted Answer · 2016-12-08 13:03:11Z

1

The parenthesis must be closed before the from:

String pattern = "SELECT\\s+(\\*|" + ownerenables + ")\\s+" + from + "\\s+" + where + "\\s*" + "(;){0,1}";

answered Dec 8, 2016 at 13:03

Maurice Perry

9,6782 gold badges15 silver badges28 bronze badges

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

4 Comments

RushSykes Over a year ago

Thanks for the answer, I tried this but it still won't match

Maurice Perry Over a year ago

Hi. I tried it, and it DOES match (at least the example you gave above)

RushSykes Over a year ago

Oops, so it says true on your computer but not on mine... Strange.... I'll go and check again, thanks

RushSykes Over a year ago

Oh yes......It's my fault, when i was doing some other tests just now I forgot about the cases where there are WHERE conditions, hha thanks! And btw I think, ah at least I thought the parenthesis is ok with the position after from, now it's proven wrong, and why is that?

coshikipix · Accepted Answer · 2016-12-08 13:00:04Z

0

you select table2 but don't select anything from it. you must specify the join between table1 and table2 if you want to do an * select.

This is your issue.

answered Dec 8, 2016 at 13:00

coshikipix

591 silver badge10 bronze badges

1 Comment

RushSykes Over a year ago

Ah here the join is not necessary, even in the real SQL system I can still do that, it't just that the result would be of no significance.

Collectives™ on Stack Overflow

Regex - SQL statement

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related