How to get the table name of the first select Oracle?

Question

I have the following oracle query:

SELECT * FROM TABELA_PESSOA A WHERE A.CPF IN (SELECT B.CPF FROM TABELA_CONTA B WHERE B.CPF = A.CPF)

I want to get the table name of the first select which is the: TABELA_PESSOA.

That’s what I put this one for select above in the method parameter below:

public String obterNomePrimeiraTabela(String query) {
    Pattern p = Pattern.compile("from\\s+\\w+");
    Matcher m = p.matcher(query);       
    if (m.find()) {
        return m.group().replace("from", "").trim();
    }
    return query;
}

The problem with this method is that if the query has two select or more, it will not work, and will end up going to the last return.

I just need to get the table from the first select.

Answer 1

Actually your regex doesn’t work because regular expressions are case sensitive by default (differentiate between upper and lower case letters). How the query has FROM and the regex from, no match found. An alternative is to change regex to FROM\\s...etc, another is to use the flag CASE_INSENSITIVE:

public String obterNomePrimeiraTabela(String query) {
    Pattern p = Pattern.compile("from\\s+(\\w+)", Pattern.CASE_INSENSITIVE);
    Matcher m = p.matcher(query);
    if (m.find()) {
        return m.group(1);
    }
    return query;
}

I also put the section corresponding to the table name in parentheses, as this forms a catch group and I can recover only this stretch using group(1) (as it is the first pair of parentheses of regex, so it is group 1). Testing:

String query = "SELECT * FROM TABELA_PESSOA A WHERE A.CPF IN (SELECT B.CPF FROM TABELA_CONTA B WHERE B.CPF = A.CPF)";
System.out.println(obterNomePrimeiraTabela(query)); // TABELA_PESSOA

---

Do not use regex

But this solution is very "naive" and prone to failure. First because it will accept any text that has the word "from" followed by anything (it will not validate if the query is valid, it will only check if it has the text "from" followed by spaces, followed by something - that is, it does not even need to be an SQL query).

Even if you guarantee that all strings are valid queries, there are still problems using regex (the shortcut \w consider letters, numbers and the character _, then regex considers that 123 and 123_ are valid names). If you want something more reliable, then in fact you need a parser sql.

There are several out there, but just to quote an example, follow a code using Jsqlparser, adapted from their own website. First we create a class that extracts tables from a query:

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.FromItemVisitor;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectVisitor;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.Union;

public class TableExtractor implements SelectVisitor, FromItemVisitor {

    private List<String> tables;

    public List<String> getTableList(Select select) {
        tables = new ArrayList<>();
        select.getSelectBody().accept(this);
        return tables;
    }

    @Override
    public void visit(PlainSelect plainSelect) {
        plainSelect.getFromItem().accept(this);

        if (plainSelect.getJoins() != null) {
            for (Iterator<?> joinsIt = plainSelect.getJoins().iterator(); joinsIt.hasNext();) {
                Join join = (Join) joinsIt.next();
                join.getRightItem().accept(this);
            }
        }
    }

    public void visit(Union union) {
        for (Iterator iter = union.getPlainSelects().iterator(); iter.hasNext();) {
            PlainSelect plainSelect = (PlainSelect) iter.next();
            visit(plainSelect);
        }
    }

    public void visit(Table tableName) {
        String tableWholeName = tableName.getWholeTableName();
        tables.add(tableWholeName);
    }

    public void visit(SubSelect subSelect) {
        subSelect.getSelectBody().accept(this);
    }

    public void visit(SubJoin subjoin) {
        subjoin.getLeft().accept(this);
        subjoin.getJoin().getRightItem().accept(this);
    }
}

Then just use it:

public String obterNomePrimeiraTabela(String query) throws JSQLParserException {
    CCJSqlParserManager pm = new CCJSqlParserManager();
    Statement statement = pm.parse(new StringReader(query));
    if (statement instanceof Select) {
        TableExtractor tableExtractor = new TableExtractor();
        List<String> tableList = tableExtractor.getTableList((Select) statement);
        return tableList.get(0);
    }
    return query;
}

...
String query = "SELECT * FROM TABELA_PESSOA A WHERE A.CPF IN (SELECT B.CPF FROM TABELA_CONTA B WHERE B.CPF = A.CPF)";
System.out.println(obterNomePrimeiraTabela(query)); // TABELA_PESSOA

It may seem much more laborious, but a parser predicts situations that regex cannot, for example if you have comments in the query:

query = "-- SELECT * FROM TABELA_PESSOA A \n"
        + " select coluna1, coluna2 from tabela1 t1 join tabela2 t2 on (t1.id=t2.id) WHERE A.CPF IN (SELECT B.CPF FROM TABELA_CONTA B WHERE B.CPF = A.CPF)";

Note that the first line is commented, so the first table is tabela1, and not TABELA_PESSOA. Thus, the parser returns correctly tabela1, but the regex returns TABELA_PESSOA, since she is only searching for anything that is after a "FROM", without taking into account the context.

Other case where regex fails:

query = "SELECT date_from as data_inicial FROM TABELA_PESSOA A";

Since regex searches for anything that is after a "from", it returns "as" as the first table. Already the parser returns correctly TABELA_PESSOA.

Of course you can change the regex to treat all these cases, but it will get so complicated that in the end it will not be worth it. A parser is much more guaranteed, since there is also the advantage of error if the query is invalid (making a regex that checks the syntax of any query is extraordinarily complicated and not worth it).

And there are many other cases to consider. If it is a INSERT with SELECT:

insert into tbl (id, nome) select id, nome from tabela_pessoa

The parser does not take the name of the table, because he understands that it is not a select statement and yes a Insert. Already the regex picks it up tabela_pessoa. Which case you want to consider?

Anyway, it also depends on the queries you are checking, it is up to you to check which cases will accept or not and choose the most appropriate solution.