Thursday, 10 April 2008

C# Regex to split multiple SQL statements

You know if you want to use SQLCommand to execute a big list of SQL Statements you cannot have the reserved SQL word GO in the script. So ideally you would want to split these statements to exclude the "GO"s and (perhaps in a transaction) execute each line separately. I wanted one of those for long time and was getting decent results with "^GO" + the multiline option, like this:

Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);

But with xml columns in tables this quickly becomes useless - Yes I script data to insert statements also. Then finally today I found one that seems to be a lot better or at least works with all my data. Here it is:

string[] lines = Regex.Split(sql, "\\s+GO\\s+|^GO\\s+", RegexOptions.Multiline);


And NO I do not know what it means - I only know that it works ;)

2 comments:

Unknown said...

Try
^\s*GO\s* ($ | \-\- .*$)

RegexOptions.Multiline, RegexOptions.IgnorePatternWhitespace, RegexOptions.IgnoreCase

This also works with comments on the go-line.

Unknown said...

Try
^\s*GO\s* ($ | \-\- .*$)

RegexOptions.Multiline, RegexOptions.IgnorePatternWhitespace, RegexOptions.IgnoreCase

This also works with comments on the go-line.