CODEWITHSUNDEEP
regexpowershell
gVoid
gVoid

Reputation: 55

Reg ex involving new line for Powershell script

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.

Upvotes: 1

Views: 25

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626747

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.

Upvotes: 1

Related Questions