Translating C# regex into Powershell

Question

I have this regex that works fine on C#:

\s*use\s+(.*?)([\r]{0,1}\n)

I have tried using it (or similar versions of it) on Powershell but it doesn't work. Can someone help me ?

Some failed attempts:

$query = [regex]::Replace($query, "\s*use\s+(.*?)([`r]{0,1}`n)", ("USE [" + $database + "]"), [System.Text.RegularExpressions.RegexOptions]::IgnoreCase -bor [System.Text.RegularExpressions.RegexOptions]::Multiline)

$query = [regex]::Replace($query, "\s*use\s+([^`r]{1,}?)([`r])", ("USE [" + $database + "]$2"), [System.Text.RegularExpressions.RegexOptions]::IgnoreCase -bor [System.Text.RegularExpressions.RegexOptions]::Multiline)

$query = $query -ireplace "\s*use\s+(.+)([\r]{0,1})", ("USE [" + $database + "]"

Some questions:

Do I need to use `r`n to get C#'s \r\n ??? (I am not yet familiar with PowerShell...)
Since my 1st attempt seems to be using a .NET function, shouldn't this function work as it works on .NET? (because I think I see some differences but I can't pinpoint the cause - is it because of the escape characters?)
How is my first regex (presented above) translated into Powershell (either with [regex]::Replace() or with -ireplace)?

UPDATE

Data to test against (I use it to change the default DB in SQL scripts):

            CREATE LOGIN [test] WITH PASSWORD=N'j-9sfjhpsojhp', 
            DEFAULT_DATABASE=[test], DEFAULT_LANGUAGE=[us_english], 
            CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
            GO

            USE test
            GO

            /****** Object:  User [test]    Script Date: 10/07/2011 16:39:48 ******/
            GO

            CREATE USER [test] FOR LOGIN [test] WITH DEFAULT_SCHEMA=[dbo]
            GO

Using a C# tool I get the USE line correctly (and I can replace it using sth like this : $2USE NewDb$2).

When using this (that uses the same regex) in Powershell, it doesn't work (I still get the USE test printed):

$database = "NewDb"
$query = Get-Content '<path to sql file>'
$query = [regex]::Replace($query, '\s*use\s+(.*?)([`r]{0,1}`n)', ("USE [" + $database + "]"), [System.Text.RegularExpressions.RegexOptions]::IgnoreCase -bor [System.Text.RegularExpressions.RegexOptions]::Multiline)
$query

UPDATE

A working solution:

$database = "NewDb"
$query = (Get-Content '<file path>') -join "`r`n"
$query = [regex]::Replace($query, '^\s*use\s+(.*?)$', ("`nUSE [" + $database + "]`n"), [System.Text.RegularExpressions.RegexOptions]::IgnoreCase -bor [System.Text.RegularExpressions.RegexOptions]::Multiline)
$query

However, newlines still don't work as in the .NET version.

mjolinor · Accepted Answer · 2014-03-08 16:04:49Z

1

Does it work if you single quote the regex? In Powershell, best practice is to single quote string literals, and only use double quotes if you explicitly want an expandable string.

Edit:

Okay, first you need to realize that Get-Content is going to return an array of strings (one per line), not a single multiline string. -Replace will still work on the array, but you don't want to match on the newlines - there aren't any.

$query_data = @'
CREATE LOGIN [test] WITH PASSWORD=N'j-9sfjhpsojhp', 
DEFAULT_DATABASE=[test], DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

USE test
GO

/****** Object:  User [test]    Script Date: 10/07/2011 16:39:48 ******/
GO

CREATE USER [test] FOR LOGIN [test] WITH DEFAULT_SCHEMA=[dbo]
GO
'@
$query_data | set-content ./query.txt

$database = "NewDb"
$query = Get-Content ./query.txt
$query = $query -replace '^\s*use\s+(.+)',"USE $database"
$query

Produces:

CREATE LOGIN [test] WITH PASSWORD=N'j-9sfjhpsojhp', 
DEFAULT_DATABASE=[test], DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

USE NewDb
GO

/****** Object:  User [test]    Script Date: 10/07/2011 16:39:48 ******/
GO

CREATE USER [test] FOR LOGIN [test] WITH DEFAULT_SCHEMA=[dbo]
GO

You can reduce the code to:

$database = "NewDb"
$query = (Get-Content '<path to sql file>') -replace '^\s*use\s+(.+)',"USE $database"

edited Mar 8, 2014 at 16:04

answered Mar 8, 2014 at 13:28

mjolinor

68.7k7 gold badges118 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

NoOne Over a year ago

Nope! I have now tried single quotes with Powershell escaping and then with C# escaping and it still doesn't work.

mjolinor Over a year ago

Can you post a sample of the data it's being used against, the results you're getting and the results you expected?

NoOne Over a year ago

Thanks! By merging the file lines and modifying the regex a bit, I got it working (I post the final code above). However, I wonder why it has a problem with escape characters like \r\n (It still doesn't work with them even when I merge the lines with -join "\r\n" -- with quotes instead of slashes).

mjolinor Over a year ago

Return and newline are escaped in Powershell as "rn" (backtick is the escape). In regex it's "\r" and "\n", so you need to join with "rn" and then match on "\r\n" in the regex.

NoOne Over a year ago

Thanks. That clears things up! I should have thought of that (I was using backtick instead of backslash in my regex). :)

Collectives™ on Stack Overflow

Translating C# regex into Powershell

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related