Regular expression to detect table names in oracle code

Asked May 30 '18 at 10:59

Active May 30 '18 at 10:59

Viewed 103 times

I would like to detect which tables are used to get data from in procedure and function code in oracle.

... from <tablename>;
... from <tablename> <alias>;
... from <tablename> <alias> where ...;
... from <tablename> <alias> (, <tablename> <alias>)* where ...
... from <tablename> <alias> (<join> <tablename> <alias> on <onclause>)* where ...

These are some of the possible constructions.

I would like to use the module 're' in python. Any insights of how can i achieve this?

This is what I came up with so far:

[(procedure.split()[0], re.findall('FROM \w+ \w* [WHERE|,|;]*', procedure, flags=re.IGNORECASE)) for procedure in procedures]

asked May 30 '18 at 10:59

JaviOverflow

1,434
2
14
31

1

You need to show us clearer input and output samples. – Tim Biegeleisen May 30 '18 at 11:00
You may possibly want to try this before: https://github.com/andialbrecht/sqlparse – bruno desthuilliers May 30 '18 at 11:07
Not sure, but probably `re.findall(r'(?i)FROM ([^(),\s]+(?:[,\s]+[^,()\s]+)*)', s)` will do this work for you. – Wiktor Stribiżew May 30 '18 at 11:13
I think you are going to find this very hard to do in a regular expression. Consider writing a proper SQL parser, using, say, `pyparsing`. An example for SQL that you might find a useful starting point here: http://pyparsing.wikispaces.com/file/view/simpleSQL.py – BoarGules May 30 '18 at 11:22

Regular expression to detect table names in oracle code

0 Answers0