Converting C# Regex To PowerShell

Question

Our Software department has a program that runs SQL scripts from a certain folder. I now need to replicate this functionality in PowerShell.

C#:

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

I have tried the following in PowerShell:

$regex = [regex]'(?im)^\s*GO\s*$'
$commands = $regex.Split($sqlfiletext)

From what I understand of Regex, this should be splitting the SQL query into commands separated by the word GO but the output of $commands is a single string that seems to be exactly the same.

Example text:

SET ANSI_PADDING OFF
END
GO


USE [DB]
SET NOCOUNT ON
GO

IF NOT EXISTS(SELECT * FROM dbo.Table)
    [[[stuff]]]
GO

IF EXISTS

EDIT: Okay this is clearly something to do with the way I am creating $regex - if I create it with the following it works, however I don't get the case insensitive option:

$regex = new-object System.Text.RegularExpressions.Regex ('^\s*GO\s*$', [System.Text.RegularExpressions.RegexOptions]::MultiLine)

Answer 1

Here are two possible ways to create 1:1 port of the C# constructor call:

$regex = new-object regex('^\s*GO\s*$', ([System.Text.RegularExpressions.RegexOptions]::MultiLine -bor [System.Text.RegularExpressions.RegexOptions]::IgnoreCase))

-bor is the same as the | in C#. The extra parentheses are necessary to not confuse the overload resolution mechanism.

For better readability, you can also leverage the built-in call to Enum.Parse:

$regex = new-object regex('^\s*GO\s*$', [System.Text.RegularExpressions.RegexOptions] "MultiLine, IgnoreCase")

I couldn't get inline options to work either, it appears they're not supported/broken in the .NET Regex constructor. They do work when using static methods like Regex.Split:

[regex]::Split($text, "(?im)^\s*GO\s*$")

Upon further examination, the original command works for me. I made an error when trying it earlier. Here's a copy/paste-ready snippet:

([regex]'(?im)^\s*GO\s*$').Split(@"
SET ANSI_PADDING OFF
END
GO


USE [DB]
SET NOCOUNT ON
GO

IF NOT EXISTS(SELECT * FROM dbo.Table)
    [[[stuff]]]
GO

IF EXISTS
"@)

Answer 2

It seems creating a Regex object doesn't support multiple flags or something because I never got it working that way.

I managed to get it splitting with the following instead:

$regex = '(?-im)^\s*GO\s*$'
$commands = $file -split $regex