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:
[sourcecode language=”powershell”]
## – Dynamically creating a variable with a value:
$SubstituteVariable = "SQLDatabase";
New-Variable $SubstituteVariable -Value "Developer";
$Tablename = "AddressBook";
[/sourcecode]
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.
[sourcecode language=”powershell”]
## [1] – Normal string one liner:
$SQLquery = "Select [firstname], [lastname] from [$SQLDatabase].[dbo].[$Tablename]";
$SQLquery;
[/sourcecode]
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.
[sourcecode language=”powershell”]
## [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’;
[/sourcecode]
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:
[sourcecode language=”powershell”]
## [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’;
[/sourcecode]
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.