Myujth

I have the following script that gets the default log and data locations for an SQL server:

$Server = '.DEV_MIGRATIONS' 
$SMOServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Server 

# Get the Default File Locations 

### Get log and data locations
$DefaultFileLocation = $SMOServer.Settings.DefaultFile 
$DefaultLogLocation = $SMOServer.Settings.DefaultLog 

if ($DefaultFileLocation.Length -eq 0) $DefaultFileLocation = $SMOServer.Information.MasterDBPath 
if ($DefaultLogLocation.Length -eq 0) $DefaultLogLocation = $SMOServer.Information.MasterDBLogPath 

$Schema_DataLocation = ($DefaultFileLocation + "Test.mdf")
$Schema_DataLocation
[Regex]::Escape($Schema_DataLocation)

I am trying to use the $Schema_DataLocation in a replace function for a schema creation script but i get errors when trying to replace the path which requires escaping regex.

What i get from the [Regex]::Escape call is:

C:\Program Files\Microsoft SQL Server\MSSQL14.DEV_MIGRATIONS\MSSQL\DATA\Test.mdf

instead of:

C:Program FilesMicrosoft SQL ServerMSSQL14.DEV_MIGRATIONSMSSQLDATATest.mdf

the replace commands:

(Get-Content $Script_SchemaCreate) | 
 Foreach-Object $_ -replace "DFILEPATH", $Schema_DataLocation | 
 Set-Content $Script_SchemaCreate


(Get-Content $Script_SchemaCreate) | 
 Foreach-Object $_ -replace [Regex]::Escape($Schema_DataLocation), "DFILEPATH" | 
 Set-Content $Script_SchemaCreate

The first replace works, but the second fails because it is trying to match a different value.

Removing [Regex]::Escape i get the following error:

The regular expression pattern C:Program FilesMicrosoft SQL ServerMSSQL14.DEV_MIGRATIONSMSSQLDATAMigration_Data.mdf is not valid.

Don't use [regex]::Escape() to escape the replacement string in a -replace operation - it isn't a regular expression, and has no special meaning inside of it.

Instead, manually escape $ chars. as $$, because $ does have special meaning in the replacement operand, namely to refer to results from the matching operation, notably capture-group results, as detailed in this answer.

Performing this manual escaping with -replace is somewhat tricky, because $ is special both in a regex and in the replacement operand, with different escaping requirements:

 # Escape a string for use as the replacementt string
 # for another -replace call:
 <string> -replace '$', '$$$$' # replace literal '$' with literal '$$'

Therefore, in this case it may be simpler to use the string-literal .Replace() method:

<string>.Replace('$', '$$') # replace literal '$' with literal '$$'

Here's a roundtrip example:

$str = 'c:program filesa-name-with-$-in-it'

# Perform substitution and output.
($new = '[DFILEPATH]' -replace 'DFILEPATH', $str.Replace('$', '$$'))

# Perform the inverse replacement.
$new -replace [regex]::Escape($str), 'DFILEPATH'

The above yields the following, proving that the substitution worked as intended:

[c:program filesa-name-with-$-in-it]
[DFILEPATH]