Removing a block of text with regex, matching first occurrences only

Question

I need to remove a stored procedure from a generated script using regex. For example, I need to remove usp_Remove:

...
GO
/****** Object:  StoredProcedure [dbo].[usp_Remove] ******/
...
GO
/****** Object:  StoredProcedure [dbo].[usp_Keep]  ******/
...
GO

I tried the following regex:

\/*\*\*\*\*\*\* Object:  StoredProcedure \[dbo\]\.\[usp_Remove\][\s\S]*GO

Which properly selects the beginning of the text. But it matches everything until the very last "GO", and I need it to match until the first occurrence of "GO" so as to remove that stored procedure only. Any help would be greatly appreciated!

Answer 1

I would use lazy matching so as not to overmatch, and use a positive look-ahead to capture only the stored procedure:

(?si)/\*{6}\s+Object:\s+StoredProcedure\s+\[dbo\]\.\[usp_Remove\]\s+\*{6}\/.*?(?=GO)

Sample code:

var txt = "YOUR_TXT";
var rx = new Regex(@"/\*{6}\s+Object:\s+StoredProcedure\s+\[dbo\]\.\[usp_Remove\]\s+\*{6}\/.*?(?=GO)", RegexOptions.Singleline | RegexOptions.Ignorecase);
var result = rx.Replace(txt, string.Empty);

Expresso:

Answer 2

Here is a pattern which uses Regex Options of Multiline (specifies the ^ will be at the beginning of the line) and Singleline which tells . to include whitespace characters:

string text = @"...
GO
/****** Object:  StoredProcedure [dbo].[usp_Remove] ******/
...
GO
/****** Object:  StoredProcedure [dbo].[usp_Keep]  ******/
...
GO";

Console.WriteLine (Regex.Replace(text,
                                 @"(^\/.+usp_Remove.+?GO)",
                                 string.Empty,
                                 RegexOptions.Multiline | RegexOptions.Singleline ));

Which results in

... 
GO

/****** Object:  StoredProcedure [dbo].[usp_Keep]  ******/ 
... 
GO

Answer 3

You may want to try this:

string resultString = null;
try {
    resultString = Regex.Replace(subjectString, @"\/.*?\[usp_Remove\].*?$\s+GO", "", RegexOptions.Singleline | RegexOptions.Multiline);
} catch (ArgumentException ex) {
    // Syntax error in the regular expression
}

---

Output:

...
GO

/****** Object:  StoredProcedure [dbo].[usp_Keep]  ******/
...
GO

---

DEMO

---

Regex Explanation:

\/.*?\[usp_Remove\].*?$\s+GO

Options: Case sensitive; Exact spacing; Dot matches line breaks; ^$ match at line breaks; Numbered capture

Match the character “/” literally «\/»
Match any single character «.*?»
   Between zero and unlimited times, as few times as possible, expanding as needed (lazy) «*?»
Match the character “[” literally «\[»
Match the character string “usp_Remove” literally (case sensitive) «usp_Remove»
Match the character “]” literally «\]»
Match any single character «.*?»
   Between zero and unlimited times, as few times as possible, expanding as needed (lazy) «*?»
Assert position at the end of a line (at the end of the string or before a line break character) (line feed) «$»
Match a single character that is a “whitespace character” (any Unicode separator, tab, line feed, carriage return, vertical tab, form feed, next line) «\s+»
   Between one and unlimited times, as many times as possible, giving back as needed (greedy) «+»
Match the character string “GO” literally (case sensitive) «GO»

Answer 4

This is a product of GREEDY regex patterns. Check this for more: http://stackoverflow.com/a/5319978/4408842

I've decided to just add a negative look-ahead ((?!GO)) to make sure that there are no other "GO" statements within the block between "/****..." and "GO". Keep in mind, the SQL script block you're taking out CANNOT have another "GO" statement within itself, or this method will stop at THAT "GO"

I've also rewritten your [\s\S]* qualifier- that's a greedy one for sure, looking for any number of whitespace or non-whitespace characters (meaning, as much of absolutely anything as possible before finding the end of the pattern), and I've rewritten \*\*\*\*\*\* as \*{6} for easier reading.

\/\*{6} Object:  StoredProcedure \[dbo\]\.\[usp_Remove\].{0,10}\*{6}\/(?!GO)[.\n]*GO