-replace To Remove '

PowerShell Help
1

I am new to powershell and inherited this code so sorry in advance. In the $3 field (,"(.+)") I have descriptions that read “FTE’s”. The ’ is causing a problem when loading the .sql statement to the database. Can anyone help me understand how to have this powershell statement replace any ’ with a blank in the format below?

{ $_ -match "`"99`",.*" } { $_ -replace "^<.*([0-9]{14}).*`"(.*)`"> `"99`",`"(.+)`",`"(.+)`",**`"(.+)`"**,`"(.+)`"" , 'INSERT INTO $(DATABASE) (LOGTIME, LOGCODE, USER, DESCRIPTION, OBJ_TYPE, OBJ_NAME) VALUES (''$1'', ''99'', '''', "Attribute "$3" was set to "$4" for element "$2".", ''Attribute'', ''$3'' ) -- $5 '    |
                            Out-File -Append "${strLogFile}.sql"
                            $intTransCount++
                        }

Sorry if this post is incorrect in format this is my first post. Thanks for the assistance.

2

It seems like you could do another replace after your first one -replace "'","''" to replace a single quote with two single quotes.

2 Likes
3

adminofthings sorry I have to ask this but I am struggling with the syntax. Can you tell me where you would put this in the statement. I tried a number of different combinations but dont understand where a second replace should appear. Thanks and sorry for my ignorance.

4

This script appears to be part of a switch variable, whereby a transaction logger .sql file is appended with dynamic INSERT statements for some later action (committed by your DBA team or an SSIS script task?).

If so, try using .Replace("'","''") on the variable that is evaluated in the switch. As this is referencing SQL objects, this should work across all inserted values (I’m leaning heavily on a SQL naming convention here; but, funkiness can happen in SQL Server, at least). The single quote will be evaluated and retained in SQL rather than having a space or special character that will create problems on the SQL side of the fence.

Putting the replacement on the switch variable might be easier than trying to fit in regex’s Search and Replace. Plus, this regex already has a lot going on without adding alteration checks. I won’t guess at how the transaction counter is looping all this. But it feels… complex and potentially infinite if break isn’t used (unlike T-SQL’s CASE and iif(), PowerShell’s switch keeps checking down the list unless you tell it to stop). You might consider setting an array or hash variable with the SQL script and looping through it outside of this action for the file writes.

Replace on switch variable

switch ($transactionString.Replace("'","''"))
{
    { $_ -match "`"99`",.*" } {<expression>; break}
    { $_ -eq ''}{<expression for empty/ whitespace>; break}
    [...]
    default {0};
}

Just a note…

The INSERT INTO appears wrongly composed as you cannot perform an insert into a SQL database directly. Then again, DATABASE could just be a variable with the FQN string value DatabaseName.SchemaName.TableName. If so, then the expression might need to be rewritten as:

"INSERT INTO $($DATABASE)(LOGTIME,... -using double quotes and $($var).

1 Like
5 
switch -Regex -File ${strLogFile}_filtered 
{
    { $_ -match "`"8`",.*" } { $_ -replace "^<.*([0-9]{14}).* `"8`",`"(.+)`",`"(.+)`"" , 'INSERT INTO $(DATABASE) (LOGTIME, LOGCODE, USER, DESCRIPTION, OBJ_TYPE, OBJ_NAME) VALUES (''$1'', ''8'', '''', ''New rule was added to cube "$2".'', ''Rule'', ''$2''  ) -- $3 '    |                             
                            Out-File -Append "${strLogFile}.sql"
                            $intTransCount++
                        }

    { $_ -match "`"99`",.*" } { $_ -replace "^<.*([0-9]{14}).*`"(.*)`"> `"99`",`"(.+)`",`"(.+)`",`"(.+)`",`"(.+)`"" -replace "'","''", 'INSERT INTO $(DATABASE) (LOGTIME, LOGCODE, USER, DESCRIPTION, OBJ_TYPE, OBJ_NAME) VALUES (''$1'', ''99'', '''', "Attribute "$3" was set to "$4" for element "$2".", ''Attribute'', ''$3'' ) -- $5 '    |
                            Out-File -Append "${strLogFile}.sql"
                            $intTransCount++
                        }

    { $_ -match "`"144`",.*" } { $_ -replace "^<.*([0-9]{14}).*`"(.*)`"> `"144`",`"(.+)`",`"(.+)`"" , 'INSERT INTO $(DATABASE) (LOGTIME, LOGCODE, USER, DESCRIPTION, OBJ_TYPE, OBJ_NAME) VALUES (''$1'', ''144'', ''$2'', ''User "$3" successfully logged in from address $4.'', ''Server'', ''SYSTEM'' ) -- $5 '    |                             
                            Out-File -Append "${strLogFile}.sql"
                            $intTransCount++
                        }

    { $_ -match "`"145`",.*" } { $_ -replace "^<.*([0-9]{14}).* `"145`",`"(.+)`",`"(.+)`"" , 'INSERT INTO $(DATABASE) (LOGTIME, LOGCODE, USER, DESCRIPTION, OBJ_TYPE, OBJ_NAME) VALUES (''$1'', ''145'', '''', ''Unsuccessful login attempt was made from address "$2".'', ''Server'', ''SYSTEM'' ) -- $3 '    |                             
                            Out-File -Append "${strLogFile}.sql"
                            $intTransCount++
                        }

}
6

I included all my code this time instead of just the line. Can I do another switch as dicey suggested before or after the switch -Regex -File ${strLogFile}_filtered to remove all the ’ from the $variables?

7

You can chain -replace operations. Example:

'my data' -replace 'my','your' -replace 'data','SQL'

1 Like
8

This was resolved by adding $_.Replace(“'”,“‘’”) so the variables for code 99 would remove any single ’ and create double ‘’ characters and the sql would load to the database properly. Probably a more elegant solutions but this is my first time writing powershell code that is not super simple. Thanks to all for the assistance.

    { $_ -match "`"99`",.*" } { $_.Replace("'","''") -replace "^<.*([0-9]{14}).*`"(.*)`"> `"99`",`"(.+)`",`"(.+)`",`"(.+)`",`"(.+)`"", 'INSERT INTO $(DATABASE) (LOGTIME, LOGCODE, USER, DESCRIPTION, OBJ_TYPE, OBJ_NAME) VALUES (''$1'', ''99'', '''', ''Attribute "$3" was set to "$4" for element "$2".'', ''Attribute'', ''$3'' ) -- $5 '    |
                            Out-File -Append "${strLogFile}.sql"
                            $intTransCount++
                        }