How to Query Arrays, Hash Tables and Strings with PowerShell

By:   |   Updated: 2018-08-13   |   Comments   |   Related: > PowerShell


Problem

When we develop solutions that require identifying what data we have or don't have, we will sometimes require an ability to query sets, definitions or find prior to conversion. For these use cases, we may use objects like arrays, hash tables and strings. The use case of these objects can stretch beyond these requirements, though these requirements provide a useful example of how we can apply these for situations where we're trying to find information that does or does not exist.

Solution

In this tip, we'll look at working with common PowerShell objects for sets, such as arrays, hash tables, and in some cases strings. As an overview of what we'll be looking at from the view of searching these objects:

  • Arrays: These can be useful when querying and working with sets. Consider a parallel database example: an array of numeric values would be like a column of numeric values in a table.
  • Hash tables: These can be useful when we need to query a unique identifying value (the key) with a definition for that value. Consider a parallel database example of a table: the key would be the name of the table and the value would be the definition.
  • Strings: These can be useful for querying data for data types within the data - we may have dates, integers, decimals, etc. within a string. Strings give us a flexible format to query data within data. Consider the parallel database example of varchars, which are strings that we can further query.

In several of these cases, we may want to convert the object or some of the object to another form before querying, or we may query the object directly. For example, we may have an array inside of a hash table, or we may want a string to be converted to an array first before we query the string.

Querying Arrays with PowerShell

We'll start with querying arrays, as we will sometimes work with hash tables that have arrays inside the values and can convert the value to an array and query further. In our first set, we'll look at two arrays - one with strings and one with ints - and look for values that either exist or don't exist. We can use the -in or -notin syntax to simply check if an array has a value that we pass to check.

$qyarray1 = @("begin", "tbQueue", "tbRunner", "tbConfig", "tbDrop", "tbFinal", "end")
$qyarray2 = @(100, 200, 300, 400, 500)

if ("tbQueue" -in ($qyarray1)) { Write-Host "tbQueue exists" }
if ("tbQueue" -notin ($qyarray1)) { Write-Host "tbQueue doesn't exist" }

if ("tbDimensionLn" -in ($qyarray1)) { Write-Host "tbDimensionLn exists" }
if ("tbDimensionLn" -notin ($qyarray1)) { Write-Host "tbDimensionLn doesn't exist" }

if ("end" -in ($qyarray1)) { Write-Host "end exists" }
if ("end" -notin ($qyarray1)) { Write-Host "end doesn't exist" }


if (100 -in ($qyarray2)) { Write-Host "100 exists"}
if (100 -notin ($qyarray2)) { Write-Host "100 doesn't exist"}

if (150 -in ($qyarray2)) { Write-Host "150 exists"}
if (150 -notin ($qyarray2)) { Write-Host "150 doesn't exist"}
Image 1

A common scenario with checking arrays is when we have an array of values and we want to check each value of that array against another array, such as an array of tables from one server that exist and compare them to an array of another set of tables from a different server. In the below code, we look at the logic of this - we iterate over our check set of tables and output if the table exists.

$checkarray = @("tbCheck", "tbQueue", "tbFinal", "tblValidate")

foreach ($value in $checkarray)
{
    if ($value -in ($qyarray1)) { Write-Host "$value exists" }
}
Image 2

Arrays can also have array values within items of an array, such as an array of 3 numbers within the first item (0th position) of the array. In the below PowerShell example, we have an array with four values and in two of the values we have arrays of values within those values. For understanding, I've used the first number to indicate the value position - such as 1 for the 0th position, 2 for the 1rst position, etc. and the second number for the value position of the second array (the array within the array). Arrays start at the 0th value, so the when we call $arlyz[0] we are calling the array of @(11,12,13).

$arlyz = @(@(11,12,13), 21, @(31,32,33,34,35), 41) 

$arlyz[0][0]
$arlyz[0][2]  
$arlyz[1][0] 
$arlyz[2][3]
$arlyz[3][0]

foreach ($arrayelementwithinarray in $arlyz[2])
{
    Write-Host ("Loop: " +$arrayelementwithinarray)
}
Image 3

As we see in the above code, if we wanted to query each item within the third value of the array (2nd position), we can iterate over it using a foreach loop for that position.

Querying Hash Tables with PowerShell

Hash tables contain key value pairs which require a unique key and can store a flexible amount of information within the value, such as storing an array or another hash table. We cannot have the exact same key value in a hash table, for example the below code will throw an error if we try to call it as we're adding the same key twice and the key must be unique. Think of a comparable example where we use an XML configuration file for our application and we call all our database connection strings the exact same key name, even though they're different servers - this would cause reference errors, since we're naming keys identically for different environments.

$qyhasherror = @{ "AppDataKey" = "<data>4</data>"; "AppDataKey" = "<data>6</data>" }
Image 3

Hash tables include the methods ContainsKey and ContainsValue, which will iterate over the keys and values and return true if a key or value exists. In the below code, we check if two keys exist and if two values exist.

$qyhash1 = @{ "AppDataKey" = "<data>4</data>"; "ConfigValue" = "messagequeue"; "InputNode" = "exit3014t" }

$qyhash1.ContainsKey("AppDataKey")
$qyhash1.ContainsKey("ConnectionString")

$qyhash1.ContainsValue("exit3014t")
$qyhash1.ContainsValue("SqlDataReader")
Image 4

The ContainsValue method will look for an example match, though with hash tables, we can query a specific value and get if text exists within the value. In the below example, we take the key names of our second hash table - ShortKey and LongValue - and for the number we check if it is equal to a value twice (one true and one false) and with the text value we use the string Contains method to see if the value has some text within it.

$qyhash2 = @{ "ShortKey" = 1; "LongValue" = "<data><versionMajor>4</versionMajor><versionMinor>182625183</versionMinor></data>" }

$qyhash2.ShortKey -eq 1
$qyhash2.LongValue.Contains("Minor")
$qyhash2.ShortKey -eq 4
$qyhash2.LongValue.Contains("fourdata")
Image 5

Querying Strings with PowerShell

In the case of strings, we can apply regular expressions or we can use methods to convert the string to an array, if we want to partition the string into groups. In the below code, we see using regular expressions as well as using the split method to create another array and compare. If we want to check if a value exists in a string and we don't need to partition the string into groups, we can use regular expressions. If we can partition the string into groups, we can use the split method and split the string into groups based on one character.

$string1 = "begin,tbQueue,tbRunner,tbConfig,tbDrop,tbFinal,end"
$checkarray = @("tbCheck", "tbQueue", "tbFinal", "tblValidate")

foreach ($value in $checkarray)
{
    ### Use RegEx
    if ($value -match $string1) { Write-Host "$value exists" }
}

foreach ($value in $checkarray)
{
    ### Use an array
    if ($value -in $string1.Split(",")) { Write-Host "$value exists" }
}
Image 6

Applied Example (Using Arrays) in PowerShell

In the below script, we have a set of tables stored in an array and we iterate over each table on a database to check if it exists in the array of tables or not. We return a message if it does not. If all databases must have a set of some objects, we can use an object like an array for validation.

Function Return-MissingTables {
    Param(
        [Parameter(Mandatory=$true)][string]$server
        , [Parameter(Mandatory=$true)][string]$database
        , [ValidateSet("2008R2","2012","2014","2016","2017")][string]$version
        , [Parameter(Mandatory=$true)][string[]]$tableset
    )
    Process
    {
        switch ($version)
        {
            "2008R2" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }

            "2012" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }

            "2014" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }

            "2016" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }
            "2017" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }
        }

        $sqlsrv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)

        foreach ($table in $sqlsrv.Databases["$database"].Tables)
        {
            if ($table.Name -notin $tableset)
            {
                Write-Warning ("Table " + $table.Name + " not found.")
            }
        }
    }
}

$set = @("tbStaging", "tbParseOne", "tbTransform2", "tbFinal")


Return-MissingTables -server "OurServer" -database "OurDatabase" -version 2017 -tableset $set

In a similar manner, we may use a combination of hash tables and string parsing to validate values of keys within a hash table. These objects can assist us to simplify tasks where we may be working with finding data within sets, finding definitions or other values by unique names, or in trying to identify specific text information.

Next Steps
  • Since we've looked at some examples of querying these objects, we can apply the inverse to storage: when working with PowerShell and storing data in objects, we may want to store unique information with definitions in hash tables, sets of objects in arrays, and data that needs to be converted to other data or parsed with strings.
  • While the hash table and string objects offer built in methods for querying, we can still query arrays compared with other arrays by using operators like -in or -notin.
  • We will query sets of data on a regular basis and in some cases, we may have to find or compare data in different formats - like finding all the tables saved in source control (files) and comparing them to all tables on a specific server (live).


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2018-08-13

Comments For This Article

















get free sql tips
agree to terms