Powershell regex to replace quotes in capture group

Question

I'm using this two line script to update a migration script - https://github.com/dotnet/efcore/issues/12911#issuecomment-505596919.

$sql = get-content .\migration.sql -raw
[regex]::replace($sql, "BEGIN\s+(CREATE (?:VIEW|TRIGGER).+?)END", "BEGIN`nEXEC('`$1');`nEND", "ignorecase,singleline") > migration.sql

The issue is that it doesn't replace the single quotes, so if I have a query like this:

BEGIN
CREATE VIEW someview AS
select lastname + ', ' + firstname from sometable
END;

I end up with

BEGIN
EXEC('CREATE VIEW someview AS select lastname + ', ' + firstname from sometable');
END;

And that doesn't work. Maybe it's because it's the end of the day and I'm feeling dense, but what do I need to do to also replace the quotes in what it captures? I know how I could do it in C#, but I'm a Powershell novice.

The regex does not match since there is `select lastname` instead of `CREATE VIEW` or `CREATE TRIGGER`. What is the rule you are trying to add to the regex? — Wiktor Stribiżew, Jan 29 '20 at 21:52

score 1 · Accepted Answer · answered Jan 29 '20 at 22:57

1

Try this:

$sql = get-content .\migration_old.sql -raw
[regex]::replace($sql, "BEGIN\s+(CREATE (?:VIEW|TRIGGER).+?)END", {param($match) "BEGIN`nEXEC('$($match.Groups[1].Value -replace "'", "''")');`nEND"}, "ignorecase,singleline") > migration_new.sql

answered Jan 29 '20 at 22:57

Jonas Tuemand Møller

615
3
7

Powershell regex to replace quotes in capture group

1 Answers1