Split SQL statements blocks using Regular expression in C#

Question

Can we write an regular expression such that it splits the stored procedure in multiple SQL statements. It should split update, delete select etc statements.

Answer 1

If you have the grammar for the stored procedure language you could use ANTLR so parse the procedure to get the relevant parts of the language out and the do any further processing necessary. It should be reletively easy to get a grammar going from scratch as well.

There would need to be a set of regex expressions to deal with the whole procedure. I.e. a regex to mach just insert statements that possible spans many lines and possible has local variables from the proc in it and so on.

Answer 2

If you are working with a known set of SQL procedures it should be pretty easy to examine them and come up with a set of regexes to split them as required.

If you are looking for something which will handle any possible set of SQL procedures then regexes wont hack it! SQL has a complex recursive grammer, and, there will always be some sub select, group by, or literal that will break your regex based parser.

As the previous poster recommended you really need a full parser such as can be generated by ANTLR or Javacc (is there a C# eqivalent?).

There are a number of SQL-92 grammer definitions available for these parser generators on the net so a large part of the work has been done for you - the remaining part - writing the parsers application logic - is still far from trivial.

Answer 3

To parse arbitrary stored procedures, you're far better off with a SQL parser. Trying to parse arbitrary SQL with regexes will amount to writing your own parser.

To parse a specific set of stored procedures, a regex may be able to do the job. You'll need to provide a few examples of the input you have and the desired output if you want a more detailed answer.

Answer 4

created a small regex based application, gets my work done.

Thanks for you comments