Split blocks of SQL statements using regex in c #
Can we write a regex to break the stored procedure across multiple SQL statements. It should split update, remove select commands etc.
Edit: my attempt at solving the problem http://tsqlparsergdr.codeplex.com/
source to share
Once you have a grammar for the language of the stored procedure, you can use ANTLR to parse the procedure to get the corresponding parts of the language and further processing. It should be easy to easily get grammar starting from scratch.
There must be a set of regular expressions to process the entire procedure. That is, the regex for mach just inserts statements that can span many lines, and possibly has local variables from proc in it, etc.
source to share
If you are working with a known set of SQL procedures, it is fairly easy to learn them and create a set of regular expressions to separate them as needed.
If you're looking for something that will handle any possible set of SQL routines then regexes won't crack it! SQL has a tricky recursive grammar, and there will always be some subset, group or literal that will break your regex based parser.
Like the previous poster, you really need a complete parser that ANTLR or Javacc can generate (is there an eqivalent CQ?).
There are a number of SQL-92 grammar definitions available for these parser generators online, so most of the work has been done for you - the rest - writing the parser application logic - is still far from trivial.
source to share
To parse arbitrary stored procedures you are much better off using a SQL parser. Trying to parse arbitrary SQL with regular expressions would be to write your own parser.
To parse a specific set of stored procedures, a regular expression can do the job. You will need to provide some examples of input and desired output if you want a more detailed answer.
source to share