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)
-bor
(bitwise OR) operator? Of course you wouldn't get theIgnoreCase
behavior unless you specify it.-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.[regex]'(?im)...
bit but it doesn't appear to work.