Using data within a SQL table to build a WHERE-Object condition

Question

I have been using PS for about 8-9 years. I want to use a SQL table to store a list of values to use in an exclusion WHERE Object clause. The source data is NOT in SQL. It is coming from a data set coming from an Outlook COM object. Basically I want to filter out emails that have certain keywords in the subject. It already has a static list that works just fine, but I would like to give the users the ability to modify this list without updating the code. Here is what I have. It looks like it 'Should' work. In fact, if I render the filter and past it into the WHERE Object, it does work. Just not with the string variable.

$dsExclude = Select-SQL "SELECT Keyword = LTRIM(RTRIM(Keyword)) FROM Email_Mining_Keywords WHERE Action='Exclude' AND WordLocation='Subject'" -ConnectionStringOrSetting $DestConnectionString  


$filter = ($dsExclude | select -ExpandProperty KeyWord | % { "`$_.Subject -NOTlike '*$_*'" }) -join ' -and '
$dsResults |   ?{$filter} | Out-GridView

It took me awhile to figure out that I needed to escape the pipe character from the source dataset while parsing the pipe character from the exclusion dataset. When rendering the filter variable, it looks great & works when manually pasting the filter.

Answer 1

The issue with your current approach is that you're evaluating a string in the -FilterScript scriptblock and, unless empty, a string is always true.

'foo' | Where-Object { 'any non empty string is true' }

What you wanted to do instead is to create an expression that is used to filter the collection:

$filter = [scriptblock]::Create(
    $dsExclude.ForEach{ "`$_.Subject -notlike '*$($_.KeyWord)*'" } -join ' -and ')

$dsResults | Where-Object $filter | Out-GridView

This approach is however inefficient and hard to understand, there is a much easier way to approach this problem by creating a regex OR pattern:

# here it is required to escape the value of `KeyWord` to match literally.
# this prevents false positives in case the value could contain special regex characters
$filter = $dsExclude.ForEach{ [regex]::Escape($_.KeyWord) } -join '|'
$dsResults | Where-Object { $_.Subject -notmatch $filter } | Out-GridView