Re^3: Parse complex SQL statement

One possibility for why the parser is failing is I think your posted SQL is invalid: unless I'm reading it wrong, you have 7 parameters to insert but 8 terms because you have a repeated comma. I also note no nested queries in the query provided, so it's also possible that I can't diagnose the issue because you haven't posted the correct target.

If this is an internal utility, you could accomplish your goal with something like

m/insert\s+into\s+(\S+)\s*\(\s*([^)]+?)\s*\)\s*
   (?:values\s*\(\s*([^)]+?)\s*\)
    |
      select\s*(.+?)\s+(?:where|order)
   )
 /ix
[download]

but this has a number of potential issues: nested queries will parse incorrectly, it fails to consider quoted names and string literals, ... Which is why I usually suggest canned code.

Update: Or misparse by me; the regular expression should read

m/insert\s+into\s+(\S+)\s*\(\s*([^)]+?)\s*\)\s*
   (?:values\s*\(\s*([^)]+?)\s*\)
    |
      select\s*(.+?)\s+(?:from\b)
   )
 /ix
[download]

Of course, your posted SQL doesn't actually correspond to the SQL posted in the original_string field in Re^2: Parse complex SQL statement.

#11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

Comment on Re^3: Parse complex SQL statement Select or Download Code

Replies are listed 'Best First'.
Re^4: Parse complex SQL statement by perl_help26 (Beadle) on Jun 16, 2014 at 09:29 UTC
Hello Kenneth, Thanks for your reply... There was a typo in my sql the successive commas shd have had "null" in between... However, this is not the issue. I have a problem with the nested queries... Also, I can't use this regexp because it's too specific and I have various sql statements with different structures... I found a module at: https://searchcode.com/codesearch/view/836428/ This seems to be parsing fine for now ... I just need to find a way to keep the hierarchy on the nested sqls...	[reply]
Re^5: Parse complex SQL statement by kennethk (Abbot) on Jun 16, 2014 at 19:22 UTC
Hierarchy is probably best maintained by anchoring front and back, and literally recursively parsing the statements. #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.	[reply]