How does one replace a variable in a string with its value?

Question

I see lots of examples of using variables in strings but this is a slightly different problem and maybe I am not phrasing it correctly in order to search for an answer. I have a string that has a variable in it, but the variable's value has not been replaced with the value. I am storing this string in a database so the settings can be changed without code changes and so that several scripts can share these values.

If I type this into the shell:

$ScriptPath = "C:\Scripts"
$LogPath = "$ScriptPath\Logs"

It works perfectly. I get back C:\Scripts\Logs

If I read that string from a database, it is a different story.

$SQL = "SELECT * FROM Script_Settings WHERE enabled = 1 AND (environment = '$Environment' OR environment = '*') AND version = $Version ORDER BY environment"
$sqlresult = Invoke-SQLCmd -ServerInstance $SettingDBServer -Database $SettingsDB -Query $SQL -TrustServerCertificate
$LogPath = $sqlresult.Log_Path
Write-Output $LogPath

$ScriptPath\Logs

Is it possible to get Powershell to evaluate the string? I suppose it is the equivalent of:

$ThePath = '$ScriptPath\Logs'
$LogPath = $ThePath

_String templating_ in PowerShell can be achieved in one of two ways: (a) defining what would normally be _expandable_ (interpolating) strings (`"..."`) as _verbatim_ strings (`'...'`) and expanding them _on demand_, with _then-current values_, using `$ExecutionContext.InvokeCommand.ExpandString()` - e.g. `$template = 'It is now $(Get-Date)'; $ExecutionContext.InvokeCommand.ExpandString($template)` - or (b) using `-f`, the string-formatting operator, with positional placeholders such as `{0}` - e.g. `$template = 'It is now {0}'; $template -f (Get-Date)`. See the linked duplicate for details. — mklement0, Aug 25 '23 at 23:08

score 1 · Accepted Answer · answered Aug 25 '23 at 22:06

A quick way to use string templates in PowerShell is to invoke $ExecutionContext.InvokeCommand.ExpandString to interpolate strings.

$ScriptPath = "C:"
$ThePath = '$ScriptPath\Logs'
$ExecutionContext.InvokeCommand.ExpandString($ThePath)

Will return

C:\Logs

score 0 · Answer 2 · answered Aug 25 '23 at 22:53

The problem is likely that what is coming back from Invoke-Sqlcmd is not a simple string. Use your debugger to stop after the Invoke-Sqlcmd and find out what $SqlResult is. Use $SqlResult.GetType().

$Sql = "SELECT 'C:\Scripts' AS THEROOT;"
$SqlResult = (Invoke-Sqlcmd -ServerInstance THEDB -Query $Sql -TrustServerCertificate).THEROOT
$LogPath = Join-Path -Path $SqlResult -ChildPath 'Logs'

I would note that if the SQL query returns more than one record, you will need to decide how to handle it.

How does one replace a variable in a string with its value?

2 Answers2