正则表达式从 SQL 查询中提取信息 [英] Regex to extract info from SQL query

问题描述

由于我是 REGEX 的新手，我无法解决以下问题.

As I am new for the REGEX i am not able to solve below thing.

请分享一些与解析器相关的链接，以便我学习.

And please share some parser related links so the i can learn it.

我在解决 SQL 语句下面的 int 时遇到问题.它的更多行添加到之前的 INPUT 中.

I am facing problem in solving int below SQL statement. Its more line added to the previous INPUT.

请帮我解决这个问题.

DECLARE
numerator   NUMBER;
BEGIN
SELECT x, y INTO numerator, denominator FROM result_table, s_Table
WHERE sample_id = 8;
the_ratio := numerator/denominator;
IF the_ratio > lower_limit THEN
INSERT INTO 
ratio VALUES (table, coloum);
ELSE
INSERT INTO onemoreTable VALUES (table, -1);
END IF;
COMMIT;
delete from     --some comment
xyz where id=17;
EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO ratio VALUES (table, 0);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
END;

输出:

SELECT from: result_table, s_Table
INSERT into: ratio
INSERT into: onemoreTable
DELETE from: xyz
INSERT into: ratio

推荐答案

这是一个使用正则表达式的基于 Perl 的解决方案:

Here is a Perl based solution using regex:

$input =~s/--.*?\n//g; # delete the comments.
$input =~s/\s+/ /g; # replace multiple white space with single space.
while($input=~m/((?:insert into)|(?:delete from)) (\w+)/ig) { 
        print "$1 : $2\n";    
}

解析 SQL 的正确方法是使用解析器而不是使用正则表达式.

The right way to parse SQL is to do it using a parser and not using regex.

这篇关于正则表达式从 SQL 查询中提取信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！