Regular expression for a single word

Question

I am using Regex.Split to split a SQL script on the keyword "GO". My problem here is that I cannot seem to get my head around how to get the Regex to do the split. My regex expression also splits on "GO" even if it's in a SQL statement like:

Insert into  x(a,b) values(‘please go get some text’,’abc’)

But I only want it to split on the keyword "GO". Any suggestions?

EDIT: I am using c#. at the moment my regex is simply:

  foreach (string batch in Regex.Split(script, "\\bGO\\b", RegexOptions.IgnoreCase))
  {
    yield return batch;
  }

Perhaps it would help if you make an example of what the matches should look like. — Daniel Rikowski, Mar 11 '09 at 13:58
if your end game is to execute the queries individually. http://stackoverflow.com/questions/40814/how-do-i-execute-a-large-sql-script-with-go-commands-from-c — jason saldo, Mar 11 '09 at 14:11

score 4 · Answer 1 · answered Mar 11 '09 at 14:00

4

This is pretty much impossible without implementing a complete SQL parser (which you probably do not want to do), in the really correct way.

An alternative would be to resort to some hacks (i.e. ignore sections of the text that are within quotes), but this will still not work if your SQL contains the text 'GO' at some other place, e.g. 'SELECT * FROM GO'.

answered Mar 11 '09 at 14:00

Martin Probst

9,497
6
31
33

+1, regex does not have the power to parse SQL, period. You may need to rethink your requirements. Writing a full SQL parser is hugely non-trivial. – bobince Mar 11 '09 at 15:23
@bobince: "hugely non-trivial"... oxymoron ? ;-) – Cerebrus Mar 13 '09 at 05:36
Err... how would that be an oxymoron? trivial would be small, non-trivial is therefore large... if anything it's redundant. – CodeRedick Mar 13 '09 at 05:44

Gordon Bell · Accepted Answer · 2009-03-13T05:51:43.423

4

Split on GOs on a line by themselves, like:

foreach (string batch in Regex.Split(script, "^GO$\\n", RegexOptions.IgnoreCase | RegexOptions.Multiline))
{
    yield return batch;
}

Depending on where your script is coming from you may need to change that to "^GO$\\r\\n"

edited Mar 13 '09 at 05:51

answered Mar 11 '09 at 14:05

Gordon Bell

13,337
3
45
64

That regex has got the anchors mixed up. Also, make sure you set multiline mode: "(?m)^GO$" – Alan Moore Mar 12 '09 at 23:00

score 3 · Answer 3 · answered Mar 11 '09 at 14:03

3

You could search for "go" on a line by itself. Not guaranteed to always work, but more likely to work.

answered Mar 11 '09 at 14:03

Joel

19,175
2
63
83

Nah, I tried this and it matches "go" in a normal statement as well – Draco Mar 11 '09 at 14:05

score 2 · Answer 4 · answered Mar 11 '09 at 14:07

You could try something like

/;\sGO\s;/i

With that you'll be covering every GO sentence, independently if they are in just one line or not (i.e. semi-colons in other lines).

If you're using queries for further execution, the you might want to add the semi-colon back to each query.

Be warned that if an occurrence of "; GO;" happens inside an insert string, then there's no way to achieve your goal without a proper SQL parser.

score 0 · Answer 5 · edited May 23 '17 at 11:47

Here's how I solved this with a regex.

var statements = Regex.Split(sql, @"^\s*GO\s*$", 
    RegexOptions.IgnoreCase | RegexOptions.Multiline);

This splits on "GO" when it is on its own line, and optionally surrounded by whitespace. Those appear to be the rules enforced by SSMS - putting GO on a line with any other statement causes an error (semi-colons or no).

Note that it will still break if your script has GO statements on their own line inside block comments. That's the only fault I can think of with it.

The bullet-proof solution would be to use SMO, as detailed in this answer.

Regular expression for a single word

5 Answers5