19

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
}
8kb
  • 10,956
  • 7
  • 38
  • 50
  • 1
    8kb, gentle nudge, but I think it would be worth accepting Andreas Covidiot's answer as it uses the actual `-in` operator in powershell – KyleMit Oct 05 '21 at 12:14

4 Answers4

23

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.

Rynant
  • 23,153
  • 5
  • 57
  • 71
12

You can use a regex:

$svr.Databases | where { $_.name -match 'testDB|master|model|msdb' } | foreach { $db.name }
Shay Levy
  • 121,444
  • 32
  • 184
  • 206
7

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

Andreas Covidiot
  • 4,286
  • 5
  • 51
  • 96
1

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 '|') + ')$')
      
mklement0
  • 382,024
  • 64
  • 607
  • 775