Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Parsing Strings From Delimiters In PowerShell


By:   |   Last Updated: 2018-06-21   |   Comments   |   Related Tips: More > PowerShell

Problem

One popular question involving strings with PowerShell involves characters which we can treat as delimiters in strings where multiple instances of those characters may be present, such as a semi-colon in a log error that appears six or seven times in the error message. Developers may want to parse some parts, such as the first or last part of the message, or middle part of the message from the string. In this tip we look at some methods we can use on strings to return pieces of one string where multiple instances of a character may exist.

Solution

We’ll start by looking at a string with seven commas in it and use the comma as the character that we may want to extract data from within or outside of, such as the word after seventh comma or the word before the first comma. We can solve this in several ways and the first way we’ll solve it is by using the methods substring and indexof. Substring works similar to T-SQL’s substring:

[string]$string = "comma one, comma two, comma three, comma four, comma five, comma six, comma seven, no comma"

$string.Substring(0,1)
$string.IndexOf(",")
$string.Substring(0,$string.IndexOf(","))
$string.Substring($string.IndexOf(","),$string.IndexOf(","))
comma

In the first line, we take the substring starting at the 0th character (nothing) to the first character, returning a c. The substring method requires the starting character and requires the length. The IndexOf method returns the first instance of the character we pass in or reports a negative if the character does not exist. We can use both of these methods to get the left set of characters from the first comma, which we do in line three. In line four, we see that we can start a string by using the IndexOf method, but we’d also have to adjust our length to match this, which we don’t.

There is another way to return characters before one character – the split method.

$string.Split(",")[0]
$string.Split(",")[1]
$string.Split(",")[3]
comma one

The split method breaks the string into an array where the character we pass in to the method (in this case, a comma) separates each element in the array. So, the first element of the array is comma one, the second is comma two and the fourth is comma four. Remember that arrays begin at the 0th character, not the first.

We can see another example of this by using a foreach loop and iterating over each element in the array:

$cnt = 1
foreach ($detailexplain in $string.Split(","))
{
    Write-Host "Element $cnt is $detailexplain"
    $cnt++
}
element

When parsing strings, two popular functions we tend to re-use is parsing right or left of a character when used as a delimiter.

In the below code, we do this with our string containing commas.

[string]$string = "comma one, comma two, comma three, comma four, comma five, comma six, comma seven, no comma"
$string.Split(",")[0]
$string.Split(",")[($string.Length - $string.Replace(",","").Length)]
no comma

Similar to T-SQL, we can use the replace function for measuring a string without characters. We’ll use the combination of Length property to get the full length, then measure the string’s length without the characters by replacing the characters with a blank.

In the below examples, we see this being done with semicolons, vertical bars and periods. We get the length of each of these strings without the chosen characters by using the replace method and length property.

"That.  Is.  Fine.".Replace(".","").Length

[string]$bars = "1|2|3|4|5|6|7"
$bars.Replace("|","").Length

$err = "Error; memory dump on partition a; run command again or contact vendor."
$err.Replace(";","").Length
numbers

We can use the above logic to determine how many of each of these specific characters (semicolons, vertical bars, and periods) are in a string.

In the below code, we’ll subtract the length of each string without any of these special characters we’re removing from the full length of the string. We can use this logic to get the right side of a string from a set of delimiters (fourth example below this), as this passes in the final delimiter number which allows our Split method to return the final part of the string after the last delimiter.

"That.  Is.  Fine.".Replace(".","").Length
[string]$bars = "1|2|3|4|5|6|7"

($bars.Length - $bars.Replace("|","").Length)

$err = "Error; memory dump on partition a; run command again or contact vendor."
($err.Length - $err.Replace(";","").Length)

### Same logic to get right side of a * delimited string:
$stardel = "One*Two*Three*Four*Five*Six*Seven*Eight*Nine"
$stardel.Split("*")[($stardel.Length - $stardel.Replace("*","").Length)]
nine

In using the Length property and Split and Replace methods, we can get the right or left side of a string from a delimiter. We can also use the Split method to get part of a string from a numbered delimiter, like we saw in some of the above examples. We can use these same functions to get strings between two delimiters, which we see below this.

Because we may sometimes forget which method to use or want a function that makes it easier to get this information faster for data, the below function allows us to get the below quickly from a line of characters where we want to extract data based on a character. It requires two parameters, the string and the character and allows us to make a selection with getting everything right or left to a character or parsing the string after a character by entering the Nth number of that character, if there are multiple instances of the character, and finally a possible parameter in case we want to get string between two identical characters.

Examples are below this:

  • Return the right or left side of characters from a set character in a string (uses $string, $character and $range parameters).
  • Return characters after one specific character (uses $string, $character and $afternumber parameters).
  • Return characters between two identical character demarcations without any of those characters in the returned string (uses $string, $character, $afternumber and $tonumber paramters). The $tonumber parameter indicates the length from the $afternumber, so if $afternumber is 2 and $tonumber is 3, $afternumber would be the third delimiter from the starting point of $afternumber.

Below shows demo strings with this function and what they return.

 
Function Extract-String {
    Param(
        [Parameter(Mandatory=$true)][string]$string
        , [Parameter(Mandatory=$true)][char]$character
        , [Parameter(Mandatory=$false)][ValidateSet("Right","Left")][string]$range
        , [Parameter(Mandatory=$false)][int]$afternumber
        , [Parameter(Mandatory=$false)][int]$tonumber
    )
    Process
    {
        [string]$return = ""

        if ($range -eq "Right")
        {
            $return = $string.Split("$character")[($string.Length - $string.Replace("$character","").Length)]
        }
        elseif ($range -eq "Left")
        {
            $return = $string.Split("$character")[0]
        }
        elseif ($tonumber -ne 0)
        {
            for ($i = $afternumber; $i -le ($afternumber + $tonumber); $i++)
            {
                $return += $string.Split("$character")[$i]
            }
        }
        else
        {
            $return = $string.Split("$character")[$afternumber]
        }

        return $return
    }
}

###Examples
$string1 = "hello:hello again:hello a third time:hello fourth:hello:hello world:hello sixth!"
$string2 = "i have,three commas,in,my string"
Write-Warning "With commas and colons"
Extract-String -string $string1 -character ":" -range Left
Extract-String -string $string1 -character ":" -range Right
Extract-String -string $string2 -character "," -range Left
Extract-String -string $string2 -character "," -range Right
Write-Warning "Within"
Extract-String -string $string1 -character ":" -afternumber 3
Extract-String -string $string2 -character "," -afternumber 2
Write-Warning "Between"
Extract-String -string $string1 -character ":" -afternumber 2 -tonumber 1
Extract-String -string $string1 -character ":" -afternumber 2 -tonumber 3
Write-Warning "With bars"
$string3 = "We will | use bars | like these | to show | that two | words can | be separated | ."
Extract-String -string $string3 -character "|" -range Left
Extract-String -string $string3 -character "|" -range Right
Extract-String -string $string3 -character "|" -afternumber 2
Extract-String -string $string3 -character "|" -afternumber 3
warning
Next Steps
  • From obtaining errors from within log messages or getting specific data from files, parsing strings from within text data can help us quickly get what we need.
  • The above function can be useful in situations where we may need to reuse it on multiple strings from within a file or message or is a good reminder of how to parse strings by character.


Last Updated: 2018-06-21


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools