How do I replace a word with a new line and a word using regex with an empty string in Powershell?

Question

Below is a sample content... I need to delete all the use database and go I'm using powershell and powershell_ise for editor:

use database_instance

go

if condition

it does not work... I actually tried this.. $out -replace "/use database_\w+\n\w+/g" — Ian, Aug 18 '14 at 02:03
why use `_` in your regex? you can try this: `/use database\n\s*\w+/g` — Tim.Tang, Aug 18 '14 at 02:06
sorry forgot to mentioned that there should be an underscore, after the underscore there should be an instance name. — Ian, Aug 18 '14 at 02:09
Edit your question to show what you want the output to look like. — briantist, Aug 18 '14 at 02:10

score 0 · Answer 1 · answered Aug 18 '14 at 02:15

0

You need to match Newline and also space after the newline:

/use database_\w+\n\s*\w+/g

answered Aug 18 '14 at 02:15

Tim.Tang

3,158
1
15
18

briantist · Answer 2 · 2014-08-18T05:31:52.777

0

$sql = @"
use database_instance

go

if condition
"@

$sql -ireplace 'use\s+\w+_\w+\s*(?:\r?\n)+\s*go' , ''

How this Works:

Using -ireplace for case insensitive regex.
Find the word use followed by one or more whitespace \s+ followed by one or more word characters \w+, then an underscore _.
One or more word characters \w+, followed by 0 or more whitespace (just in case)
A non-capturing group (?:) since we don't need the result, this is just to encapsulate a newline that accounts for windows and unix line endings. It consists of an optional CR followed by a LF, and this is matched 1 or more times.
Followed by 0 or more whitespace \s* then the word go.
Replace it with nothing!

This does leave some empty space, but that shouldn't be too big of an issue since the SQL parser won't care.

Note

In your comments you said you tried:

$out -replace "/use database_\w+\n\w+/g"

Be aware that powershell does not use /regexhere/ syntax. The forward slashes are treated as literals, so the flags you specified are as well. The replace is global by default so you don't need g anyway.

edited Aug 18 '14 at 05:31

answered Aug 18 '14 at 02:20

briantist

45,546
6
82
127

Your sample works, but when I try it on my data, it does not work.. don't know why... my data came from a batch file it fetches the stored procedure and put it on a variable which is the "$out" variable. – Ian Aug 18 '14 at 02:43
Post the output of `[RegEx]::Escape($out)` – briantist Aug 18 '14 at 03:33
Please see below.. use\ margining_prd\ go\ \ IF\ EXISTS\ – Ian Aug 18 '14 at 05:25
Still not replacing, In the powershell, I used the dllgen.bat to connect to the sybase and get the sp that i wanted. and then the save it to the output variable ($out)... after that need to remove some stuff in the string (SP)... – Ian Aug 18 '14 at 05:42

How do I replace a word with a new line and a word using regex with an empty string in Powershell?

2 Answers2

How this Works:

Note