title: Multiline search and replace using C#/class Regex
body: <p>I've some stored procedures that contain stuff like this:</p>
  
  <pre><code>SELECT columnA, columnB, COUNT(*) AS "COUNT" INTO temporaryTable
  FROM tableA
  WHERE columnA = "A"
    AND ISNULL(columnB, "B") = "B"
  GROUP BY columnA, columnB
  HAVING columnA = "A"
    AND ISNULL(columnB, "B") = "B"
  SELECT * FROM temporaryTable -- There is not necessary to have an empty line between two instructions.
  </code></pre>
  
  <p>As said, there are procedures, so many instructions are in the same script.</p>
  
  <p>I load each of theses procedures in a <a href="https://msdn.microsoft.com/en-us/library/system.text.stringbuilder%28v=vs.110%29.aspx" rel="nofollow">StringBuilder</a> (that contains the same script that shown above).</p>
  
  <p>I want to remove the HAVING part if (and only <code>IF</code>!). It is exactly the same as in the WHERE part (as above).</p>
  
  <p>So I immediatly thought at the regular expressions.</p>
  
  <p>I've something like this:</p>
  
  <pre><code>    static string RemoveHaving(Match m)
      {
          if (m.Groups[3].Value == m.Groups[7].Value)
          { /* WHERE == HAVING */
              Console.WriteLine("Same");
              return string.Concat(m.Groups[1].Value, m.Groups[9].Value);
          }
  
          Console.WriteLine("Not Same");
          return m.Groups[0].Value;
      }
  
      static void Main(string[] args)
      {
          // For the example:
          StringBuilder procedure = new StringBuilder();
          procedure.Append(@"
              SELECT columnA, columnB, COUNT(*) AS "COUNT" INTO temporaryTable
              FROM tableA
              WHERE columnA = "A"
                AND ISNULL(columnB, "B") = "B"
              GROUP BY columnA, columnB
              HAVING columnA = "A"
                AND ISNULL(columnB, "B") = "B"
              SELECT * FROM temporaryTable -- There is not necessary to have an empty line between two instructions.");
  
          Regex reg = new Regex(@"((.*)where(.*)([\s^]+)group\s*by(.*)([\s^]+))having(.*)([\s^]+(SELECT|INSERT|UPDATE|DELETE))",
              RegexOptions.Compiled |
              RegexOptions.IgnoreCase |
              RegexOptions.Multiline);
  
          string newProcedure = reg.Replace(procedure, (MatchEvaluator)RemoveHaving);
          Console.WriteLine("---");
          Console.WriteLine(newProcedure);
          Console.WriteLine("---");
      }
  </code></pre>
  
  <p>It works, but it does not seem to be the best way...</p>
  
  <p>How do I detect safely the end of the HAVING?</p>
  
  <p>How would you manage this work?</p>