Powershell – Using RegEx

UPDATE March 2, 2010:
I have found the answer to my issue. After posting my problem on several forums the best and winning answer I got was from mjolinor on Microsoft Technet, here is the link to the forum post.  http://tinyurl.com/ybam2no. I tagged along on a post already close to what I was trying to do.

Still fuzzy on RegEx but back to loving Powershell!


$file = gc ./testsmall.sql
$file2 = @()

$file |% {

switch -regex ($_){

“^\s*$” {continue}

“^(\s*)END\s*$” {
if ($last_END){
$file2 += ($matches[1] + ‘SELECT @P_ID’)
$file2 += $_
$last_END = $false
$file2 += $_
$last_END = $true
default {$file2 += $_;$last_END = $false}


$file2 | set-content ./testsmall.sql


Another note to consider. I was using SQL Server Management Studio to do my testing. I would run the script against the file. It appears some of the issues I was having (this happened recently on a different file) was with SSMS its self. I am seriously going to have to reconsider how I use that tool in the future.


I am humbly asking for some assistance with my current dilemma.

I am currently using Powershell to parse through a SQL script being generated by a tool converting an Oracle database to SQL Server. I go through the file an remove extra code that is not needed and causing issues in the application. I have most of it working but just can’t seem to figure out this issue.

This is the code I am using in Powershell.


$l = @”


$m = @”

(get-content ./testsmall.sql) | %{$_ -ireplace “$l”, “$m” } | set-content ./testsmall.sql


I am running it against this snippet of a larger file testing.

testing code 1

I really need to just add in a “SELECT @P_ID” on line 45. So I am using the replace “method” in the “Get-Content” the CmdLet to replace the two (2) END transaction statements and insert the SELECT @P_ID at the end of the UPDATE statement.

The problem I am having is it finds the first “END” (line 32) statement  in the insert statement and replaces that. It also finds the first “END” statement at the end of the “UPDATE” statement (line 46) and replaces that leaving me with an orphaned “END” statement.

test code 3

I don’t think I am far off but wanted to solicit some feedback from the RegEx gurus out there. I have a “Cheatsheet” for RegEx but it is still pretty fuzzy to me.

I have a tool called RegExMagic that I used to generate this and other code (that is working). When tested in the tool it finds the two “END” statements I am looking for but can’t for the life of me figure this out.

Please post comments here or hit me up on Twitter @SQLAJ

Thanks for any help.




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s