Learning C# methods in PowerShell don’t like -1

Trying to be smart getting me learned!

Words: 432

Time to read: ~ 3 minutes

It’s been a while…

…since I’ve written a blog post and there’s no better way to get back into something than to just start doing. Even if it’s just a throwaway, little post.

So here’s mine. Hope you enjoy it.

Don’t ask why…

… but recently I was trying in PowerShell to split a string up into its individual characters. So, as an example, ‘SQL Server’ would become the ('S', 'Q', 'L', ' ', 'S', 'e', 'r', 'v', 'e', 'r') collection of characters.

I also wanted the character before it and after it as well…

This Char	Prev Char	Next Char
S	r	Q
Q	S	L
L	Q
	L	S
S		e
e	S	r
r	e	v
v	r	e
e	v	r
r	e	S

The Easiest Way…

…that I found was to simply ask for the character a certain position (or index) in the string.

	$String = 'SQL Server'
	for ($i = 0; $i -lt $String.Length; $i++ ) {
	[PSCustomObject]@{
	'This Char' = $string[$i]
	'Prev Char' = $string[$i -1]
	'Next Char' = $string[($i +1) % $String.Length]
	}
	}

view raw UsingForSyntax.ps1 hosted with ❤ by GitHub

As you can see we had to add a little got’cha to our code: ($i +1) % $String.Length

This is because, without the modulo operator (what remains when we divide the numbers), PowerShell looks for the next index (10) and returns nothing since there is essentially nothing in index 10.

So we ask PowerShell what 10 modulo the length of the string is ( 10 % 10) and the remainder is 0. This way we can wrap back around to the start again!

However, coming from a database background…

…this may seem like a pretty simple exercise, especially since we know that PowerShell has a Substring method.

	$String = 'SQL Server'
	0..($String.Length – 1) \| ForEach-Object –Process {
	$String.Substring($_, 1)
	}

view raw SubstringStringSplit.ps1 hosted with ❤ by GitHub

However, what happens when we try and go backwards i.e. $String.Substring(-1, 1)?

Nope!

Exception calling “Substring” with “2” argument(s): “StartIndex cannot be less than zero.

Try as I might…

…I couldn’t get any way that used “.whatever()” to work. Substring; nope, Chars; nada.

The moment that I passed in a -1 I just saw a sea of red.

Thankfully I’ve been frequenting the PowerShell slack channel lately and they were able to let me know why.

sifb [Nov 12th at 10:01 PM]
@Shane O’Neill I think the $array[-1] loop-around is a powershell convenience, and doesn’t exist in C# / the lower level .Net libraries

So there we go…

…even though we may be used to SubString, it doesn’t mean that it is the best way for us to go. This is technically a new language and there are going to be tips and tricks for doing things that we don’t yet know about!

Half the fun is rooting them out, finding them, and slowly, slowly watching your code improve and knowing why.

I would have written a shorter [post], but I did not have the time.

Using Multiple Delimiters to Split Strings with PowerShell

I appear to be going for the Ronseal titles lately…

Words: 725

Time to read: ~4 minutes

Continue reading “Using Multiple Delimiters to Split Strings with PowerShell”

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.

	#… get some results to work with…
	$sqlcmdParams = @{
	ServerInstance = 'localhost\SQLSERVER2K16'
	Database = 'master'
	Query = @'
	SELECT TOP(10)
	name
	FROM dbo.spt_values
	WHERE name IS NOT NULL
	AND LEN(name) != 0
	ORDER BY name;
	'@
	};
	$dbResults = Invoke-Sqlcmd @sqlcmdParams;

	#…now check the substring function…
	foreach ($row in ($dbResults.name)) {
	[PSCustomObject]@{
	RowName = $row
	RowSubString = $row.Substring(5, 100)
	};
	};

	<#
	# Error message:
	# Exception calling "Substring" with "2" argument(s): "startIndex cannot be larger than length of string"
	#>

view raw

SubstringLengthError.ps1

hosted with ❤ by GitHub

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
 }
}

PowerShellStringLength — 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…


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

PowerShellSubstringWorks — 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 ($dbResults.name)) {
  [PSCustomObject]@{
    RowName = $row
    RowSubString = $row.Substring(5)
  };
};

The more you know… 🙂