Replace filepath string in file

Question

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 Files\Microsoft SQL Server\MSSQL14.DEV_MIGRATIONS\MSSQL\DATA\Test.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 Files\Microsoft SQL Server\MSSQL14.DEV_MIGRATIONS\MSSQL\DATA\Migration_Data.mdf is not valid.

mklement0 · Accepted Answer · 2018-11-15T13:56:46.663

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 files\a-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 files\a-name-with-$-in-it]
[DFILEPATH]

Replace filepath string in file

1 Answers1