JavaScript regular expression to find table names with alias in SQL code

Question

I have a very large SQL query. I am trying to scrape tables names using javascript out of SQL query. (I provide SQL query in a text file as input)

  select t1.name, t1.id, t1.address, t2.units,t2.sale
  from SCHMEA1.candy_customers_azim_056 as t1
  inner join SCHEMA1.candy_sales_history_set t2
    on (t1.custid = t2.ORIGTN_ACCT_NO)

What could be the regular expression to achieve something like this ?

Tables:
candy_customers_azim_056 as t1
candy_sales_history_set t2

Have you tried anything yet? Have you put your example text into regex101.com to see if you can work it out that way? Are you looking for just the regex, or are you looking for something that can help you to get the alias' to begin with? — Ajaypayne
– Ajaypayne, Commented Sep 5, 2018 at 18:40
@Ajaypayne I have been trying something like this regex101.com/r/73a5GA/6 — Azim Shaik
– Azim Shaik, Commented Sep 5, 2018 at 18:55
Do you consider queries like: select * from table1, table2? — cn0047
– cn0047, Commented Sep 5, 2018 at 19:32

user8675309 · Accepted Answer · 2018-09-05 18:50:26Z

4

(from|join)\s+\w+\.((\w*)[as ]+(\w+))

should return what you're looking for in the second capture group.

answered Sep 5, 2018 at 18:50

user8675309

6411 gold badge8 silver badges26 bronze badges

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

2 Comments

Azim Shaik Over a year ago

this is helpful

cn0047 Over a year ago

What about next case: left join candy_sales_history_set4?

cn0047 · Accepted Answer · 2018-09-05 19:29:38Z

0

You may try this one as well:

/(from|join)\s+(\w+\.)?(\w+)(\s+as)?(\s+\w+)?/gmi

You can see examples here.

edited Sep 5, 2018 at 19:29

answered Sep 5, 2018 at 19:22

cn0047

17.2k7 gold badges61 silver badges75 bronze badges

Collectives™ on Stack Overflow

JavaScript regular expression to find table names with alias in SQL code

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related