从查询中获取表名 [英] get table name from query

问题描述

对于许多人来说，我的问题应该很简单支持我有以下SQL，我想使用正则表达式获取表名:

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);
        }

但是，如果表名包含模式名(xyz.table1)，我的表达式将包含所有内容.我的问题是...我需要在此查询上修改什么，以便仅向我返回没有模式/所有者的表名?

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

拉斐尔·莫伊塔

推荐答案

也许可以这样尝试

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));

输出:

table1 
table1 

编辑

我刚刚意识到部分($ | \\ s + [WHERE，JOIN，START \\ s + WITH，ORDER \\ s + BY，GROUP \\ s + BY])'不能正常工作，因为在我的输入中，我在表名之后放置了"whatever"，无论如何都可以找到它.

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.

它不像您那样工作，因为您使用的是 [WHERE，JOIN，START \\ s + WITH，ORDER \\ s + BY，GROUP \\ s + BY] 而不是<代码>(WHERE | JOIN | START \\ s + WITH | ORDER \\ s + BY | GROUP \\ s + BY).例如， [abc] 等于(a | b | c)，因此它说正则表达式引擎接受该集合中的任何字符，而不是单词 abc .将您的模式改进为

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);

这篇关于从查询中获取表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！