How Do I Extract Selected Columns Given An Oracle SQL String?

OK, this might seem too tough to be posted here so I beg your pardon. Been working on this for almost a week. I need to extract all selected columns in a given Oracle SQL String. I

Solution 1:

Given that Oracle SELECT lists can get pretty complex (having to account for all of the cases you mention, plus subqueries, tablename.columnname constructs, quoted aliases, etc), you probably want to go beyond Regular Expressions and actually parse the SQL query then pull the tokens out of the parsed output.

To that end, you have a couple of different options, none of which are all that easy, but may be able to solve your problem

• If you're willing to use Perl, you can probably make SQL::Parser do what you want.
• You can get a 90 free trial download of gsqlparser if you want a java-based solution, which would be helpful if this is a one-time project.
• There is this - SQL92 parser, which is a free download but of unknown license, and I'm not totally sure if it can handle any Oracle-specific weirdness.
• you can use Antlr to generate a SQL parser with a java interface based on this guy's work, which is based on CREATE TABLE syntax but can be adapted readily to handle SELECT syntax (or you can search for antlr sql grammar and find a premade one pretty easily)