java - get table name from query

Question

My question should be simple for many of you Supouse I have the following SQL and I want to get the table name using regexp:

SELECT name, age FROM table1

Using this expression I can get that ok

Pattern p = Pattern.compile(".*FROM\s+(.*?)($|\s+[WHERE,JOIN,START\s+WITH,ORDER\s+BY,GROUP\s+BY])", Pattern.CASE_INSENSITIVE);
        Matcher result = p.matcher(pSql);
        if (result.find()) {
            lRetorno = result.group(1);
        }

But, in case the table name contains the schema name (xyz.table1) my expression brings everything. My question is ... what do I need to modify on this query to only return me the table name without schema/owner?

Any help would be extremely apreciated Regards

Raphael Moita

Answer 1

Maybe try it this way

String data1="SELECT name, age FROM table1 whatever";
String data2="SELECT name, age FROM schema.table1 whatever";

Pattern p=Pattern.compile("from\s+(?:\w+\.)*(\w+)($|\s+[WHERE,JOIN,START\s+WITH,ORDER\s+BY,GROUP\s+BY])",Pattern.CASE_INSENSITIVE);

//test
Matcher m=p.matcher(data1);
while(m.find())
    System.out.println(m.group(1));
m=p.matcher(data2);
while(m.find())
    System.out.println(m.group(1));

output:

table1 
table1 

Edit

I just realized that part ($|\s+[WHERE,JOIN,START\s+WITH,ORDER\s+BY,GROUP\s+BY]) doesn't work as it should because in my input i placed "whatever" after table name and it was found anyway.

It doesn't work like you because you are using [WHERE,JOIN,START\s+WITH,ORDER\s+BY,GROUP\s+BY] instead of (WHERE|JOIN|START\s+WITH|ORDER\s+BY|GROUP\s+BY). For example [abc] is equal to (a|b|c) so it says regular expression engine to accept any character from that set, not a word abc. Improve your pattern to something like

Pattern p=Pattern.compile("from\s+(?:\w+\.)*(\w+)(\s*$|\s+(WHERE|JOIN|START\s+WITH|ORDER\s+BY|GROUP\s+BY))",Pattern.CASE_INSENSITIVE);