0

Can anyone tell me how to replace the name of all the table names in a the SELECT/FROM statements?. I'm looking of a way that works well across vanilla queries as well as more complex ones with sub-queries and joins.

I.e.

New table name: new_table Original query: SELECT * from table;

Result query: SELECT * FROM new_table;

Thanks a lot,

j

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jorge Del Conde
  • 265
  • 1
  • 4
  • Possible duplicate of [How to change a table name using an SQL query?](https://stackoverflow.com/questions/886786/how-to-change-a-table-name-using-an-sql-query) – Anurag Srivastava Mar 04 '19 at 15:11
  • I really didn't get your question so you need to alias your table with another name during initializing your query or you need to rename the entire table name. – Michel Hanna Mar 04 '19 at 15:14
  • Based on a quick glance at the Apache Calcite website, it supports a very standard SQL syntax which means you should be able to do "Select * from table as 'new_table'" The word "as" may or may not be needed. I have not used Apache Calcite so I'm not positive of that as an answer. – SteveB Mar 04 '19 at 15:21
  • Use regex perhaps? – Nae Mar 04 '19 at 16:50
  • One of Calcite's features is an SQL Parser. I want to be able to parse the original query and navigate to all of the apropiate SqlIdentifier nodes that contain a table name so that I can change the actual table name. – Jorge Del Conde Mar 05 '19 at 04:12

1 Answers1

0

If your queries are as simple as what you're proposing, you should be able start by parsing the query which will give you a SqlSelect object. From there you can use getFrom to check if it's the table you want to change and setFrom to change it.

If you want to handle more complex queries, you should be able to implement the SqlVisitor interface to find all occurrences of the table to replace.

Michael Mior
  • 28,107
  • 9
  • 89
  • 113
  • My queries are definitely not that simple :) They can be extremely complex. I implemented an SqlVisitor and noticed that things got complicated really quickly. The biggest issue is it's hard to know if the SqlIdentifier is a table-name, a field, etc .. – Jorge Del Conde Mar 06 '19 at 16:15
  • On a similar note, I'm wondering if there's any easy way to know how many different tables a query references. – Jorge Del Conde Mar 06 '19 at 16:18
  • You're right that in general, it's a hard problem. I would suggest starting with the simplest possible case you outlined above and write a test and then continue expanding and writing tests. Ultimately I'm not sure there's a great way to ensure that you cover every possible case, but it shouldn't be too tough to cover a reasonable class of queries. – Michael Mior Mar 06 '19 at 17:21