Quickblog on PowerShell Here-String/Splatting simple formatting

Sometime is nice to find simple samples that can make life easy. Here’s a quick sample on how you can use this string manipulation to make it readable. Also, I included a way to dynamically create a PowerShell variable from an existing (it may be useful for someone) just as a proof-of-concept.

In this sample I’m going to create a T-SQL script and display it in my PowerShell console. First, let’s create some variables that will be use to replace some values in our string:


## - Dynamically creating a variable with a value:
$SubstituteVariable = "SQLDatabase";
New-Variable $SubstituteVariable -Value "Developer";

$Tablename = "AddressBook";

The above code will create three PowerShell variable but we are end up using only the $SQLDatabase and the $Tablename in our T-SQL script.

The next few PowerShell code will show you a simple ways to initialize a string variable holding the T-SQL scripts.

1. One-liner sample – All acceptable except when using complex Multi-line T-SQL Scripts.


## [1] - Normal string one liner:
$SQLquery = "Select [firstname], [lastname] from [$SQLDatabase].[dbo].[$Tablename]";
$SQLquery;

2. Using Here-String/Splatting, you can change the above sample and make it a multi-line string. This still is a One-liner, and it use the tab in front of the column names. *note: In PowerShell V3.0 the tab will be ignore but in PowerShell V2.0 it will work OK.


## [2] - Using Here-String/Splatting.  This string contains manual tabs done in and editor the it was copy/paste to the console: (bug in V3 console only.  ISE works.)

$SQLquery1 = @"
Select
	[firstname],
	[lastname]
  from [$SQLDatabase].[dbo].[$Tablename]
"@;

$SQLquery1;
Write-Host "$SQLquery1" -ForegroundColor 'Yellow';

3. The last Here-String sample we are using the .NET ‘-f’ string formatter which will allow you to replace the values for {0} and {1} place holder. In this sample code inside the string i’m hard coding tab as `t (tick symbol plus t) which in this case it works OK in both PowerShell V2 and V3:


## [3] - Using Here-String/Splatting with .NET '-f' formatting:
              $SQLquery2 = @"
Select
`t[firstname],
`t[lastname]
  from [{0}].[dbo].[{1}]
"@ -f $SQLDatabase, $tablename;

Write-Host "$SQLquery2" -ForegroundColor 'Cyan';

4. When using a PowerShell editor (ISE or others) an invalid Here-String variable PowerShell will give an error if you try to use tabs on each line of the code: (can’t use white space in editor with Here-String)

[tab] $x = @”
[tab] Testing
[tab] Testing
[tab] “@;

Basically, Here-String variable works great when you want to store a multi-line string but have some caveats:

1. PowerShell v3 console only. Manual tabs are ignored.
2. When using splatting (@”..”@) the @” can begin in any column position but the terminating “@ ends on the beginning of the new line. See sample #3.
3. When using a PowerShell editor Whitespaces in a Here-String block of code are not allowed.