How to use Powershell Where-Object like an IN statement

Question

I have the following code which works:

foreach ($db in $svr.Databases | 
         where-object {
         $_.name -eq "testDB" 
         -or $_.name -eq "master"
         -or $_.name -eq "model"
         -or $_.name -eq "msdb" } )
{
  write-output $db.name
}

Is a cleaner way to do this?

Something like:

foreach ($db in $svr.Databases | 
         where-object {$_.name -in "testDB, master, model, msdb" } )    
{
  write-output $db.name
}

Vamoose

8kb, gentle nudge, but I think it would be worth accepting Andreas Covidiot's answer as it uses the actual -in operator in powershell – Karrikarrie

Answer 1

Use the -contains operator. Like:

$dbs = "testDB", "master", "model", "msdb"

foreach ($db in ($svr.Databases | where-object {$dbs -contains $_.name  } )) {
    write-output $db.name
}

Use help about_Comparison_Operators to learn more about this and other comparison operators.

Update:

PowerShell v3 has added the -in operator. The example in the original question will work in v3.

Bohon

Aren't you missing a paren there? – Aden

Yes, I did miss a paren. Thanks, fixed. – Bohon

The example in the original question won't quite work - as it is a comma delimited string - not an array. – Triceratops

Answer 2

You can use a regex:

$svr.Databases | where { $_.name -match 'testDB|master|model|msdb' } | foreach { $db.name }

Scavenger

Answer 3

Powershell has an -in operator since Powershell 3

$srv.Databases | where Name -in "master","model","msdb" | write-output { $_.Name }

Further Reading: Docs on Comparison Operators > Containment Operators

Ritualism

Answer 4

Just to add the most concise solution:

([array] $svr.Databases.Name) -match '^(testDB|master|model|msdb)$'

^{For an explanation of the RHS regex and the ability to experiment with it, see this regex101.com page.}

The above relies on:

• Member-access enumeration to return the .Name property values of all elements of the $svr.Databases collection.

  • The [array] cast ensures that the result is always an array, even if only one property value happens to be returned.

• The ability of comparison operators such as -match, the regular-expression matching operator, to act on arrays as the LHS, in which case they act as filters and return the sub-array of matching elements.

  • By contrast, the -contains operator and its PSv3+ counterpart, -in - the only difference between the two being the order of operands - only support a literal and therefore single comparison value, and only ever return a single, Boolean ([bool]) value that indicates whether the comparison value is contained (equal to an element of) the array operand.

  • Note: In the case at hand, the literal comparison values happen not to contain regex metacharacters, so they can be used as-is; if this assumption cannot be made, [regex]::Escape() must be applied to the comparison values:

    $vals = 'testDB', 'master', 'model', 'msdb'
    ([array] $svr.Databases.Name) -match `
      ('^(' + ($vals.ForEach({ [regex]::Escape($_) }) -join '|') + ')$')
    

Structuralism