给定一个Oracle SQL字符串，如何提取选定的列? [英] 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.

我需要提取给定Oracle SQL String中的所有选定列.它应该通过以下测试用例:

I need to extract all selected columns in a given Oracle SQL String. It should pass the following test cases:

// single column test
select col1 from dual
    // ^ should match "col1"

// multiple column test
select col1,col2 from dual
    // ^ should match "col1", "col2"

// multiple space test
select   col1   ,  col2   from   dual
    // ^ should match "col1", "col2"

// "distinct" tests
select distinct col1 from dual
    // ^ should match "col1"
select distinct col1, col2 from dual
    // ^ should match "col1", "col2"

// "distinct" with whitespaces tests
select   distinct   col1   from   dual
    // ^ should match "col1"
select   distinct   col1  ,  col2  from   dual
    // ^ should match "col1", "col2"

// "as" tests
select col1 from dual
    // ^ should match "col1"
select colA as col1 from dual
    // ^ should match "col1"
select colA as col1, col2, col3 from dual
    // ^ should match "col1", "col2", "col3"
select col1, colB as col2, col3 from dual
    // ^ should match "col1", "col2", "col3"
select col1, col2, colC as col3 from dual
    // ^ should match "col1", "col2", "col3"

// "as" tests with whitespaces tests
select    colA    as    col1,    colB    as    col2,    colC    as    col3    from    dual
    // ^ should match "col1", "col2", "col3"


// "distinct" with "as" tests
select distinct colA as col1 from dual
    // ^ should match "col1"
select distinct colA as col1, colB as col2, col3 from dual
    // ^ should match "col1", "col2", "col3"
select distinct colA as col1, col2, colC as col3 from dual
    // ^ should match "col1", "col2", "col3"


// function test
select funct('1','2') as col1 from dual
    // ^ should match "col1"
select col1, funct('1','2') as col2 from dual
    // ^ should match "col1", "col2"
select col1, colB as col2, funct('1','2') as col3 from dual
    // ^ should match "col1", "col2", "col3"

我在Java中尝试了以下RegEx

I tried the following RegEx in Java

 ((?<=select\ )(?!distinct\ ).*?(?=,|from))
 ((?<=select\ distinct\ ).*?(?=,|from))
 ((?<=as\ ).*?(?=,|from))
 ((?<=,\ ).*?(?=,|from))(?!.*\ as\ ) // <- Right, I'm guessing here

对它们进行或运算，但是我不能简单地通过上面的所有测试用例. (我正在使用此工具来验证我的Regex).

OR-ed them together but I can't simply pass all the test cases above. (I'm using this tool to validate my Regex).

我尝试搜索SQL求值器，但是没有找到一个提取所有列而不对真实数据库执行该求值器并且假定所有引用的表和函数都存在的任何求值器.

I tried searching for SQL evaluator but can't find any that extracts all columns without executing it against a real database and that assumes all referenced tables and functions exist.

Java ReGex，可以通过测试的免费SQL评估程序(不需要真实的数据库)，或者可以通过这两个更好的答案.假定SQL始终采用Oracle 11g格式.

A Java ReGex, a free SQL Evaluator (that doesn't need a real database) that can pass the tests, or anything better that those two are the acceptable answers. The assumption is that the SQL is always in Oracle 11g format.

推荐答案

鉴于Oracle SELECT列表可能变得非常复杂(必须考虑到您提到的所有情况，以及子查询，tablename.columnname构造，加引号的别名等) )，您可能想超越正则表达式并实际解析SQL查询，然后将令牌从解析的输出中拉出.

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

• 如果您愿意使用Perl，则可以使 SQL :: Parser 做您想要的.
• 如果您想要基于Java的解决方案，则可以免费获得 gsqlparser 的90个免费试用版下载. ，如果这是一次性项目，这将很有帮助.
• 有一个- SQL92解析器，可以免费下载.但许可证未知，而且我不确定是否可以处理任何Oracle特有的怪异现象.
• 您可以使用Antlr使用基于

• 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)

这篇关于给定一个Oracle SQL字符串，如何提取选定的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！