Use Powershell To Remove Everything Before or After A Character

Working with strings is important when handling automation processes especially regarding server names and the SQL Server instances. Administrators define their own naming conventions and usually stay away from unique characters in the string. With SQL Server we have a slash “\” that is required to define the SQL instance so storing the server info is a little different.

I found a simple way to seperate the server and SQL instance names in Powershell using SPLIT, here’s an example:

$array = $SQLinstance.Split("\")   ## split the string into an array

$array[0]    ## This is the server name
$array[1]    ## This is the SQL Instance name

Let’s also take a look at (the hard way) using the Powershell Substring method to find characters before or after the slash in a SQL Server instance name assigned to a variable.

Powershell Substring

To demonstrate the Subtring method we are just assigning a simple string with the slash in it and pulling out the characters from the start point of 0 and 11 characters deep. The first parameter is the starting point and the second is the number of characters to return.

$SQLinstance = "OHBigServer\SQL234"    ## assign a string

$SQLinstance.Substring(0, 11)     ## get before the slash

$SQLinstance.Substring(12, 6)     ## get after the slash

In this example we counted the number of characters to find the slash and returned everything before it. The second Substring started at the slash (12) position and returned everything after it. Notice that id did NOT include the slash in the result.

Finding A Character(s)

To automate this we need to search for the character in the string automatically and use its position in our Substring() statement. Luckily there is another method called Indexof() that allows us to get this number easily. We can use this to replace the 2nd value in our Substring() statement, since that is the stopping point.

$SQLinstance = "OHBigServer\SQL234"    ## assign a string

$SQLinstance.IndexOf("\")     ## get the numeric position

Powershell Substring() and Indexof()

We can now use the Indexof() and Substring() to automatically return characters before and after the slash. This gives us just the Servername without the SQL instance and the SQL instance without the Servername.

$SQLinstance = "OHBigServer\SQL234"    ## assign a string

## replace the 11 with Indexof()
     $SQLinstance.Substring( 0, $SQLinstance.IndexOf("\") )

## still has slash \ ?
     $SQLinstance.Substring($SQLinstance.IndexOf("\"), 6)     

## add 1 to remove "\"
     $SQLinstance.Substring($SQLinstance.IndexOf("\") + 1, 6)     

If you ran this you will notice that using the start point of 0 for the “before” characters is not the same as the “after” characters. Since the index is the (dbtales dot com) location of the slash “\” as the start point we are getting it as part of the results, we had to add a character to fix it.

Using Length, Indexof and Substring Together

To resolve the 2nd parameter for Substring we need to know the number of characters so Powershell does not throw an error. Powershell has a method called “Length” that we can apply to the string and do a little math with.

$SQLinstance = "OHBigServer\SQL234"

$SQLinstance.Length     ## length of the entire string

## subtract the location of slash "\"
     $SQLinstance.Length - $SQLinstance.IndexOf("\")

## Just return the end portion or SQL instance,..  but still a slash ?
     $SQLinstance.Substring($SQLinstance.IndexOf("\"), $SQLinstance.Length - $SQLinstance.IndexOf("\"))

## Remove the slash
     $SQLinstance.Substring($SQLinstance.IndexOf("\")+1, $SQLinstance.Length - $SQLinstance.IndexOf("\")-1)

The last 2 commands require a little explanation. We returned the end portion of the string, the SQL instance, but it still included the slash. To remove this we needed to increment the first Substring parameter by 1 BUT this causes an error because the end parameter is now 1 character too long. So we decrement the 2nd paramter to fix it.

Testing for Slash Using the IF Statement

In some processes I’ve had to grab the first part of the SQL Instance in order to use Test-Connection and verify it is up before connecting. The challenge is that there are default SQL instances without an instance name and therefore no slash.
Here I use the Indexof method in an Powershell IF statement to see if there is a slash character and therefore I need to Substring the servers name out of it.

$SQLinstance = "OHBigServer\SQL234"

if($SQLinstance.IndexOf("\") -gt 0) {
     $servername = $SQLinstance.Substring(0, $SQLinstance.IndexOf("\"))
} else {
     $servername = $SQLinstance
}

write-host $servername

---