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
}

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
– KyleMit ♦, Commented Oct 5, 2021 at 12:14

Rynant · Accepted Answer · 2016-03-01 19:27:19Z

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.

edited Mar 1, 2016 at 19:27

answered Aug 24, 2011 at 17:08

Rynant

24.6k5 gold badges61 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Christine Over a year ago

Aren't you missing a paren there?

Rynant Over a year ago

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

Martin Smith Over a year ago

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

Shay Levy · Accepted Answer · 2011-08-25 08:55:00Z

12

You can use a regex:

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

answered Aug 25, 2011 at 8:55

Shay Levy

128k34 gold badges193 silver badges210 bronze badges

Comments

Andreas Covidiot · Accepted Answer · 2021-10-06 18:44:29Z

7

+250

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

edited Oct 6, 2021 at 18:44

answered Feb 26, 2019 at 21:17

Andreas Covidiot

4,8335 gold badges55 silver badges106 bronze badges

Comments

mklement0 · Accepted Answer · 2022-03-11 00:18:27Z

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 '|') + ')$')
```

Collectives™ on Stack Overflow

How to use Powershell Where-Object like an IN statement

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related