Reg ex involving new line for Powershell script-CodePudding

I have a long text file that looks like this:

("B3501870","U00357"),
INSERT INTO [dbo].[Bnumbers] VALUES
("B3501871","U11019"),
("B3501899","U28503"),

I want every line before INSERT to end not with , but with ; instead. So the end result should look like this:

("B3613522","U00357");
INSERT INTO [dbo].[Bnumbers] VALUES
("B3615871","U11019"),
("B3621899","U28503"),

I tried multiple ways to achieve this but it does not appear to work with multiple lines. One way I tried was like this:

(Get-Content -path C:\temp\bnr\list.sql -Raw) -replace ",\nINSERT", ";\nINSERT" | Add-Content -Path C:\temp\bnr\test.sql

Tried with

[io.file]::ReadAllText("C:\temp\bnr\list.sql")

hoping it treat the file as one giant string but to no avail.

Any way to tell PS to find comma newline INSERT and do changes to it?

,\nINSERT

works on Sublime text with reg ex but not in PS.

CodePudding user response：

You can use

(Get-Content -path C:\temp\bnr\list.sql -Raw) -replace ',(\r?\nINSERT)', ';$1'

Or,

(Get-Content -path C:\temp\bnr\list.sql -Raw) -replace ',(?=\r?\nINSERT)', ';'

See the regex demo.

The ,(?=\r?\nINSERT) regex matches a comma that is immediately followed with an optional CR char, then LF char, then INSERT text. The ,(\r?\nINSERT) variation captures the CRLF/LF ending INSERT string into Group 1, hence the $1 backreference in the replacement pattern that puts this text back into the result.