Transact-SQL to Powershell: Substring

In my ongoing attempt to learn Powershell to help automate my workloads, I’ve come across the need to use the Transact-SQL SUBSTRING() function but, in using it, I got the following error:

Now if you are like me, that is very hard to read but the error is saying

StartIndex cannot be larger than length of string

Compare-Object ‘SQLServer’ ‘PowerShell’

The main difference that I can see when using SUBSTRING() in SQL Server versus in PowerShell is that SQL Server is very forgiving.

If you have a string that is 20 characters longs and you ask for everything from the 5th character to the 100th character, SQL Server is going to look at this, see that the string does not go to the 100th character, and just give you everything that it can.

PowerShell on the other hand, while being amazingly forgiving with some things….

Examples:

• ​"a" + 2 =  a2
• "a" * 2 = aa
• 2 + 2 = 4
• "2" + 2 = 22

…is surprisingly less forgiving than SQL Server here.

If we checked the length of the results we can see the length of each individual row:

foreach ($row in ($dbResults.name)) {
  [PSCustomObject]@{
  RowName = $row
  RowLength = $row.Length
 }
}

So PowerShell goes to find the 5th to the 100th character, sees that the 100th character is outside the length of the string, and freaks out!

The PowerShell Hammer…

…can also be a PowerShell Scalpel as well. You can get as precise as you need to and in this case, with the error complaining about the length, we should probably be more specific about the length we want.

So let’s get more specific about the length! Now we could go and input all the different values for substring function but let’s get a bit more dynamic about it.

It is PowerShell after all…

#...now check the substring function...
#...with proper values...
foreach ($row in ($dbResults.name)) {
  [PSCustomObject]@{
    RowName = $row
    RowSubString = $row.Substring(5, ($row.Length) - 5)
  }
}

So there we go, SQL Server substring and PowerShell substring are basically the same. We just have to be concise about it!

Update: 2017-08-15

Thanks to Michael Villegas ( blog | twitter ) for pointing out in the comments that PowerShell has a simpler syntax to deal with this.

While SQL Server requires 3 arguments for the substring function (expression, start, length); PowerShell has the same thing but it also has a simpler syntax for getting the characters from a starting point all the way to the end.

#...simpler syntax...
foreach ($row in ($dbResults.name)) {
  [PSCustomObject]@{
    RowName = $row
    RowSubString = $row.Substring(5)
  };
};

The more you know… 🙂