Monday, February 22, 2010 #

How to split SQL file by GO statement

In some situations when using scripts to create/update database in code, I had to split input SQL script file by GO statements, as SqlCommand doesn’t really understand it. But in some place, I’ve came to a strange-looking code that does exactly that but… in really weird and NOT correct way. So here we go.

How NOT to write:

//separator char not used in TSQL
char ch = '^';
 
//Ed: str is the text from SQL batch file
str = str.Replace("GOTO", "GGGG");
str = str.Replace("Goto", "GGGG");
str = str.Replace("\r\nGO\r\n", ch.ToString()) ;
str = str.Replace("\r\nGO \r\n", ch.ToString()) ;
str = str.Replace("\r\nGO", ch.ToString()) ;
str = str.Replace(" Go\r\n", ch.ToString());
str = str.Replace(" go\r\n", ch.ToString());
str = str.Replace(" Go \r\n", ch.ToString());
str = str.Replace(" go \r\n", ch.ToString());
 
str = str.Replace(" GO\n", ch.ToString());
str = str.Replace("\nGO\n", ch.ToString());
str = str.Replace(" go\n", ch.ToString());
str = str.Replace("\ngo\n", ch.ToString());
 
str = str.Replace("\r\ngo", ch.ToString()) ;
str = str.Replace("\r\nGo", ch.ToString()) ;
 
//return GOTO statements
str = str.Replace("GGGG", "Goto");
            
return  str.Split(new char[]{ch});

Weird huh? :) All this instead of simple Regular Expression, something like this one (worked for me so far):

return Regex.Split(str + "\n", @"^\s*GO\s*$", RegexOptions.IgnoreCase | RegexOptions.Multiline);

posted @ Monday, February 22, 2010 12:08 PM | Feedback (0)