Regex to extract info from SQL query

Question

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.

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

Please help me to slove this.

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;

OUTPUT:

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

Sneaky, with the comment there. For real SQL there are more complex scenarios, and you probably want an SQL parser. What language is this, anyway? — Kobi
– Kobi, Commented Aug 25, 2010 at 5:35
I agree with Kobi, unless you can guarantee very basic queries you probably are best to get a proper SQL parser. — Craig
– Craig, Commented Aug 25, 2010 at 6:27

codaddict · Accepted Answer · 2010-08-25 06:08:24Z

1

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";    
}

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

answered Aug 25, 2010 at 6:08

codaddict

457k83 gold badges501 silver badges537 bronze badges

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

Collectives™ on Stack Overflow

Regex to extract info from SQL query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related