1

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)
  • What's wrong with using the -bor (bitwise OR) operator? Of course you wouldn't get the IgnoreCase behavior unless you specify it. – Daniel B Jun 30 '16 at 15:58
  • I don't know much about -bor but I thought it was a numbers thing, not string splitting? And I can't find a way to include two RegEx options in the New-Object command. Please enlighten me as to either way of achieving this. – Deadly-Bagel Jun 30 '16 at 16:07
  • This site explains about the [regex]'(?im)... bit but it doesn't appear to work. – Deadly-Bagel Jun 30 '16 at 16:13
3

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
"@)
| improve this answer | |
  • Ahhhh, that's what you meant by using -bor, I've never found any documentation on using it like that. Thanks =) – Deadly-Bagel Jul 1 '16 at 9:40
  • What the... and my original attempt is now working... Feh, never mind. Appreciate the comprehensive answer – Deadly-Bagel Jul 1 '16 at 9:44
1

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.