Powershell And SQL With Multiple NotLike Filters For Database Names

I'm querying a system database on multiple servers with PowerShell. The original SQL query has several "AND name NOT LIKE" in the WHERE clause. I can do this in PowerShell with -NotLike, but the number of "AND name NOT LIKE" is fairly long. Is there any way to make the "AND name NOT LIKE" list simpler in PowerShell?

SQL AND name NOT LIKE

AND name NOT LIKE 'DBA%'
AND name NOT LIKE 'DB_LOS%'
AND name NOT LIKE 'JIRA%'
AND name NOT LIKE 'Report%'
AND name NOT LIKE 'AdventureWorks%'
AND name NOT LIKE '%APM%'
AND name NOT LIKE '%AZEU%'
AND name NOT LIKE '%TM1%'
AND name NOT LIKE '%Utility%'
AND name NOT LIKE '%detail_epro%'
AND name NOT LIKE '%config_epro%'
AND name NOT LIKE '%summary_epro%'
AND name NOT LIKE '%lookup%'
AND name NOT LIKE '%meldb%'
AND name NOT LIKE '%Sandbox%'
AND name NOT LIKE '%Scribe%'
AND name NOT LIKE '%Stage%'
AND name NOT LIKE '%Staging%'
AND name NOT LIKE '%STG%'
AND name NOT LIKE '%SSRS%'
AND name NOT LIKE '%snapshot%'

PowerShell

Get-SqlDatabase -ServerInstance DBServer | Where-Object {$_.name -notlike "db_los*" -and $_.name -notlike "DBA*"} | Select-Object name, status

Eventually this will be automated to go across several servers.

Instead of using multiple -notlike operators, you can also put all strings to not like in a pipe | separated string and use the regex -notmatch operator like this:

# create a regex string using the OR character as separator

# this line anchors the names at the beginning of the line
$excluded = '^(?:db_los|DBA|JIRA|Report|AdventureWorks)' 
# append the names that may occur anywhere in the line
$excluded += '|APM|AZEU|TM1|Utility|detail_epro|config_epro|summary_epro|lookup|meldb|Sandbox|Scribe|Stage|Staging|STG|SSRS|snapshot'

Then use this like:

Get-SqlDatabase -ServerInstance DBServer | 
    Where-Object {$_.name -notmatch $excluded} |
    Select-Object name, status

The above matches Case-Insensitive. If you need case-sensitivity, replace -notmatch with -cnotmatch

Regex details:

> Match either the regular expression below (attempting the next alternative only if this one fails)
> ^ Assert position at the beginning of the string
> (?: Match the regular expression below
> Match either the regular expression below (attempting the next alternative only if this one fails)
> db_los Match the characters “db_los” literally
> | Or match regular expression number 2 below (attempting the next alternative only if this one fails)
> DBA Match the characters “DBA” literally
> | Or match regular expression number 3 below (attempting the next alternative only if this one fails)
> JIRA Match the characters “JIRA” literally
> | Or match regular expression number 4 below (attempting the next alternative only if this one fails)
> Report Match the characters “Report” literally
> | Or match regular expression number 5 below (the entire group fails if this one fails to match)
> AdventureWorks Match the characters “AdventureWorks” literally
> )
> | Or match regular expression number 2 below (attempting the next alternative only if this one fails)
> APM Match the characters “APM” literally
> | Or match regular expression number 3 below (attempting the next alternative only if this one fails)
> AZEU Match the characters “AZEU” literally
> | Or match regular expression number 4 below (attempting the next alternative only if this one fails)
> TM1 Match the characters “TM1” literally
> | Or match regular expression number 5 below (attempting the next alternative only if this one fails)
> Utility Match the characters “Utility” literally
> | Or match regular expression number 6 below (attempting the next alternative only if this one fails)
> detail_epro Match the characters “detail_epro” literally
> | Or match regular expression number 7 below (attempting the next alternative only if this one fails)
> config_epro Match the characters “config_epro” literally
> | Or match regular expression number 8 below (attempting the next alternative only if this one fails)
> summary_epro Match the characters “summary_epro” literally
> | Or match regular expression number 9 below (attempting the next alternative only if this one fails)
> lookup Match the characters “lookup” literally
> | Or match regular expression number 10 below (attempting the next alternative only if this one fails)
> meldb Match the characters “meldb” literally
> | Or match regular expression number 11 below (attempting the next alternative only if this one fails)
> Sandbox Match the characters “Sandbox” literally
> | Or match regular expression number 12 below (attempting the next alternative only if this one fails)
> Scribe Match the characters “Scribe” literally
> | Or match regular expression number 13 below (attempting the next alternative only if this one fails)
> Stage Match the characters “Stage” literally
> | Or match regular expression number 14 below (attempting the next alternative only if this one fails)
> Staging Match the characters “Staging” literally
> | Or match regular expression number 15 below (attempting the next alternative only if this one fails)
> STG Match the characters “STG” literally
> | Or match regular expression number 16 below (attempting the next alternative only if this one fails)
> SSRS Match the characters “SSRS” literally
> | Or match regular expression number 17 below (the entire match attempt fails if this one fails to match)
> snapshot Match the characters “snapshot” literally