extract fields and value from sql string with regexp

Question

Say I have a string like this:

$sql = "insert into BENEF (BEN_NOM,BEN_PRENOM,BEN_ADR1,BEN_CREATED_ON,BEN_ENABLED) values ('Houn','Luc','l\"oule (1)','2014-09-17 17:45:08','1');";

How to write a regexp to extract just :

1 => BENEF

2 => BEN_NOM,BEN_PRENOM,BEN_ADR1,BEN_CREATED_ON,BEN_ENABLED

3 => 'Houn','Luc','l\"oule (1)','2014-09-17 17:45:08','1'

I tried :

$motif =  '/insert into (.*) \(([^)]*)\) values \(([^)]*)\)/';  
preg_match($motif,$sql , $out);

But unfortunaly "(1)" data corrupt results

in other words how to extract TABLE X Y with :

insert into TABLE (X) values (Y);

why this doesn't work ?

$motif =  '/^insert into (.*) \(([^)]*)\) values \(([^)]*)\);$/'

Regards

finaly find :

'/^insert into (\S+) \((.*)\) values \((.*)\);$/'

I know this isn't the answer you want to hear, but parsing a SQL statement with regular expressions is wrong. In layman's terms, regular expressions parse "regular" grammar, and code/script languages are "complex" grammar, which makes parsing it with RegEx extremely complicated, if you want it done right. Here's an answer with a better explanation (the question is different but the answer can be applied here, as well) - http://stackoverflow.com/questions/133601/can-regular-expressions-be-used-to-match-nested-patterns — qJake, Sep 17 '14 at 16:04

score 0 · Answer 1 · edited May 23 '17 at 10:33

Regular expressions are designed to parse "regular" grammar, and code languages (SQL, C++, Python, etc) are "context-free" grammars (more complicated) - hence, Regular expressions should not be used to parse code.

Your expression would work if you did not have nested parenthesis inside your values, but that is an unrealistic limitation to try and add to your application just to support some parsing you can easily do without Regular expressions.

Some reference answers that echo this theory:

https://stackoverflow.com/a/1758162/334053

https://stackoverflow.com/a/20449679/334053

https://stackoverflow.com/a/133684/334053

extract fields and value from sql string with regexp

1 Answers1