Regular Expression to retrieve column and tables from SQL statement

Question

I need to get all columns and all tables from an SQL statement, let's say:

SELECT u.id, u.name FROM table1 as t1 LEFT JOIN table2 as t2 ON t2.id=t1.id INNER JOIN table3 as t3 ON t3.id=t1.id WHERE t1.id=1

The output needs to be:

Columns[] = { {"u","id"}, {"u","name"} }
Tables[] = { {"t1","table1"}, {"t2","table2"}, {"t3","table3"} }

I tried the following but i just get the two first table names

^SELECT\s*.*\s*FROM\s*([a-z_]*).*\sJOIN\s*([a-z_]*).*$

chrylis -cautiouslyoptimistic- · Accepted Answer · 2013-11-29 12:36:47Z

3

The SQL grammar is too complicated to extract this sort of information with a regular expression; you'll need a SQL parser.

You might consider ANTLR's predefined SQL grammar or JSqlParser.

answered Nov 29, 2013 at 12:36

77.5k21 gold badges127 silver badges167 bronze badges

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

1 Comment

yeah! that's a nice tip. I will try.