PowerShell command to replace a chunk of text in a file

Question

I am trying to replace a chunk of text in a file using PowerShell. For instance, I have a .sql file and I know the exact chunk of an SQL script that needs to be replaced in that particular file. After reading through some of the PowerShell replace examples it looks like PowerShell returns the content of the file in an array (each line representing one entry in the array).

For example:

GO
:on error exit
GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)] COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE [$(DatabaseName)]
.......
.........
..... MORE SQL SCRIPT

I want to replace the text till USE [$(DatabaseName)] in the above file.

Answer 1

Here's how I would go about this. First, when you need to get the entire contents of a file for purposes like replacing text over multiple lines, don't use Get-Content. Instead, use [IO.file]::ReadAllText().

Then use the -replace operator, for example:

[IO.File]::ReadAllText("$pwd\foo.sql") -replace `
   '(?s).*?(USE \[\$\(DatabaseName\)\].*)$',"foo`n`$1" > foo.sql

Here I'm replacing the beginning text with "foo". Also note that to get the regex used by -replace to match across newlines I prefix the regex with (?s) - single-line mode.

Mjolinor brings up a good point, in the case where the replacement text has characters that could be interpreted as regex special variables e.g. $1, $2, etc. While you can use [regex]::escape() to escape the regex there's still PowerShell code that will interpret $<something> as a variable or start of a sub-expression. In this case, it's pretty simple to work around by just capturing the part you want to keep with the -replace operator and then prepend the new text in a second step e.g.:

$keep = [IO.File]::ReadAllText("$pwd\foo.sql") -replace `
            '(?s).*?(USE \[\$\(DatabaseName\)\].*)$','$1'
$newText + $keep > foo.sql

Note that in the replace in this case, I use single quotes around $1 which prevents PowerShell from interpreting any special PowerShell characters. It's kind of like a verbatim string in C#.