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….


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

…is surprisingly less forgiving than SQL Server here.

#… get some results to work with…
$sqlcmdParams = @{
ServerInstance = 'localhost\SQLSERVER2K16'
Database = 'master'
Query = @'
FROM dbo.spt_values
AND LEN(name) != 0
ORDER BY name;
$dbResults = Invoke-Sqlcmd @sqlcmdParams;
#…now check the substring function…
foreach ($row in ($ {
RowName = $row
RowSubString = $row.Substring(5, 100)
# Error message:
# Exception calling "Substring" with "2" argument(s): "startIndex cannot be larger than length of string"

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

foreach ($row in ($ {
  RowName = $row
  RowLength = $row.Length
As you can see, none of these are near 100

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… check the substring function...
#...with proper values...
foreach ($row in ($ {
    RowName = $row
    RowSubString = $row.Substring(5, ($row.Length) - 5)

I should probably be more concise with my T-SQL scripts too

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 ($ {
    RowName = $row
    RowSubString = $row.Substring(5)



The more you know… 🙂


Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

7 thoughts on “Transact-SQL to Powershell: Substring”

  1. Substring, like many other library functions, should have been written to make development easier, not harder. C# and PowerShell have the same faulty logic, by throwing an exception rather than being helpful and giving as much as they can of what you asked for. TSQL’s substring behavior is the better option. Unfortunately for us, Microsoft made the wrong choice again.

    1. I honestly do prefer TSQL’s way of doing it. I think familiarity wins out overall.

      That being said, I also like that PowerShell has a shortcut with just stating the start position, then it will return everything.
      If I wanted to return from the 1st character up to the 5th character and some strings are not that long, well then I’d be screwed because I haven’t tried or thought of a workaround for it yet.

      I defer to your judgement on C# matters because, as Nicholas Cain said, “the closest I would have gotten to C# would have been a tart orange”

  2. Yup, you’re absolutely right & I bow to your superior knowledge 🙂

    I think what caught me was trying to map them together.
    You NEED 3 arguments in SQL Server but apparently not in PowerShell.

    I like your method though; shorter and I won’t run into those errors.
    Thanks Michael!

  3. HI Shane, that is an interesting article, however I think that the other way around the issue would be to use only one parameter in the substring function, that way powershell will extract everything from that Pont onwards. Example

    $a = ‘hello world’

Leave a Reply

%d bloggers like this: